作者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