[SQL ] 呃 ... 這一句 SQL 我寫了一個下午
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
推
09/03 21:24, , 1F
09/03 21:24, 1F
推
09/06 19:18, , 2F
09/06 19:18, 2F
推
09/06 20:50, , 3F
09/06 20:50, 3F
推
09/08 18:59, , 4F
09/08 18:59, 4F
推
09/18 02:54, , 5F
09/18 02:54, 5F