[SQL ] 呃 ... 這一句 SQL 我寫了一個下午

[cleanwind (骨傲風清(掛網))]

C# 加上 Access ，由於為了符合對方奇怪的列表，

再加上自己又有點懶，所以正規化沒有做的非常徹底，

於是為了顯示某列表結果 ... 寫了一個下午的 SQL指令。


其實也就只有 `record`, `person`, `pay`, `rate` 四張表跑來跑去而已 XD"

我只是想來說，放在螢幕中間還頗有美感的 XD"



string sqlStr = "SELECT DISTINCT `time`, `name`, `record`.`number`, " +
"(SELECT COUNT(*) FROM `person` WHERE `record_id`=`record`.`id`), " +
"(SELECT COUNT(*) FROM `person` WHERE `record_id`=`record`.`id` AND `id` IN "+
"(SELECT DISTINCT `person_id` FROM `pay` WHERE `person_id` = `person`.`id` " +
" AND `i_nt` > 0 OR i_money > 0)), (SELECT COUNT(*) FROM `person` WHERE " +
"`record_id`=`record`.`id` AND `id` IN (SELECT DISTINCT `person_id` " +
" FROM `pay` WHERE `person_id` = `person`.`id` AND `i_bill` > 0)), " +
"(SELECT COUNT(*) FROM `person` WHERE `record_id`=`record`.`id` AND " +
"`id` IN (SELECT DISTINCT `person_id` FROM `pay` WHERE " +
"`person_id` = `person`.`id` AND `i_nt` > 0 OR `i_money`>0 OR `i_card`>0))," +
"(SELECT SUM(`i_nt`) FROM `pay` WHERE `person_id` IN (SELECT `id` FROM " +
"`person` WHERE `record_id` = `record`.`id`))+(SELECT " +
"SUM(`i_money`*`rate`.`rate`) FROM `pay` LEFT JOIN `rate` ON " +
"`pay`.`i_money_id`=`rate`.`money_id` WHERE `i_money` > 0 AND " +
" FORMAT(`rate`.`date`, 'yyyymmdd')=FORMAT(`record`.`time`, 'yyyymmdd') AND " +
"`person_id` IN (SELECT `id` FROM `person` WHERE `record_id` =`record`.`id`))"+
", `record`.`note` FROM `record` LEFT JOIN `customer` ON " + "
`customer`.`id`=`record`.`customer_id` WHERE `travel_id` <> 0 AND `time` >= #"+
 year.ToString() + "/" + month.ToString() + "/1# AND " +
 "`time` < #" + dateTimePicker1.Value.AddMonths(1).Year.ToString() + "/" +
 dateTimePicker1.Value.AddMonths(1).Month.ToString() + "/1# ORDER BY `time`";

--
※ 發信站: 批踢踢實業坊(ptt.cc) 
◆ From: 120.101.9.24
推 bobju:這種寫法不好維護,日後欄位變動要改語法可能會辛苦.
推 hirabbitt:帥啊XDDD
推 eggeggss:藝術品啊...不過被後輩幹譙的可能性...
推 yzfr6:這麼多Sub Query?
推 cyr1216:這整段執行起來不會很慢嗎?........