作者cleanwind (骨傲風清(掛網))
看板Database
標題[SQL ] 呃 ... 這一句 SQL 我寫了一個下午
時間Fri Sep 3 18:51:03 2010
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
1F：推 bobju:這種寫法不好維護,日後欄位變動要改語法可能會辛苦. 09/03 21:24
2F：推 hirabbitt:帥啊XDDD 09/06 19:18
3F：推 eggeggss:藝術品啊...不過被後輩幹譙的可能性... 09/06 20:50
4F：推 yzfr6:這麼多Sub Query? 09/08 18:59
5F：推 cyr1216:這整段執行起來不會很慢嗎?........ 09/18 02:54