如何制作有1-31号日期为列的报表，横排变成竖排，我的程序总是超时！ (200分)

改进一下
SELECT
UserFrom,
sum(case when d=1 then
s else
0 end) AS '1号',
sum(case when d=2 then
s else
0 end) AS '2号',
sum(case when d=3 then
s else
0 end) AS '3号',
sum(case when d=4 then
s else
0 end) AS '4号',
sum(case when d=5 then
s else
0 end) AS '5号',
sum(case when d=6 then
s else
0 end) AS '6号',
sum(case when d=7 then
s else
0 end) AS '7号',
sum(case when d=8 then
s else
0 end) AS '8号',
sum(case when d=9 then
s else
0 end) AS '9号',
sum(case when d=10 then
s else
0 end) AS '10号',
sum(case when d=11 then
s else
0 end) AS '11号',
sum(case when d=12 then
s else
0 end) AS '12号',
sum(case when d=13 then
s else
0 end) AS '13号',
sum(case when d=14 then
s else
0 end) AS '14号',
sum(case when d=15 then
s else
0 end) AS '15号',
sum(case when d=16 then
s else
0 end) AS '16号',
sum(case when d=17 then
s else
0 end) AS '17号',
sum(case when d=18 then
s else
0 end) AS '18号',
sum(case when d=19 then
s else
0 end) AS '19号',
sum(case when d=20 then
s else
0 end) AS '20号',
sum(case when d=21 then
s else
0 end) AS '21号',
sum(case when d=22 then
s else
0 end) AS '22号',
sum(case when d=23 then
s else
0 end) AS '23号',
sum(case when d=24 then
s else
0 end) AS '24号',
sum(case when d=25 then
s else
0 end) AS '25号',
sum(case when d=26 then
s else
0 end) AS '26号',
sum(case when d=27 then
s else
0 end) AS '27号',
sum(case when d=28 then
s else
0 end) AS '28号',
sum(case when d=29 then
s else
0 end) AS '29号',
sum(case when d=30 then
s else
0 end) AS '30号',
sum(case when d=31 then
s else
0 end) AS '31号'
FROM
(select day(datetime) as d, sum(Points) as s ,UserFrom
from letters
where parent=0
group by day(datetime),UserFrom
) t
group by UserFrom
这是按照大富翁写的，已经测试正确。
你的就是
SELECT
goods_name,
sum(case when d=1 then
s else
0 end) AS '1号',
sum(case when d=2 then
s else
0 end) AS '2号',
sum(case when d=3 then
s else
0 end) AS '3号',
sum(case when d=4 then
s else
0 end) AS '4号',
sum(case when d=5 then
s else
0 end) AS '5号',
sum(case when d=6 then
s else
0 end) AS '6号',
sum(case when d=7 then
s else
0 end) AS '7号',
sum(case when d=8 then
s else
0 end) AS '8号',
sum(case when d=9 then
s else
0 end) AS '9号',
sum(case when d=10 then
s else
0 end) AS '10号',
sum(case when d=11 then
s else
0 end) AS '11号',
sum(case when d=12 then
s else
0 end) AS '12号',
sum(case when d=13 then
s else
0 end) AS '13号',
sum(case when d=14 then
s else
0 end) AS '14号',
sum(case when d=15 then
s else
0 end) AS '15号',
sum(case when d=16 then
s else
0 end) AS '16号',
sum(case when d=17 then
s else
0 end) AS '17号',
sum(case when d=18 then
s else
0 end) AS '18号',
sum(case when d=19 then
s else
0 end) AS '19号',
sum(case when d=20 then
s else
0 end) AS '20号',
sum(case when d=21 then
s else
0 end) AS '21号',
sum(case when d=22 then
s else
0 end) AS '22号',
sum(case when d=23 then
s else
0 end) AS '23号',
sum(case when d=24 then
s else
0 end) AS '24号',
sum(case when d=25 then
s else
0 end) AS '25号',
sum(case when d=26 then
s else
0 end) AS '26号',
sum(case when d=27 then
s else
0 end) AS '27号',
sum(case when d=28 then
s else
0 end) AS '28号',
sum(case when d=29 then
s else
0 end) AS '29号',
sum(case when d=30 then
s else
0 end) AS '30号',
sum(case when d=31 then
s else
0 end) AS '31号'
FROM
(select day(bill_date) as d, sum(quantity) as s ,goods_name
from vdish
where (bill_date >= '2003-02-01') AND (bill_date <= '2003-02-03')
group by day(bill_date),goods_name
) t
group by goods_name
 
to : 火魂 ，这么晚了还在回答我的问题真是感动！我试试，过会给你发消息 ！
对了，各位英雄，用交叉表怎么用呀，学生 学历有限，想多学习，望 指点！
 
soul,您好，我在SQL 分析器中得到数据了，我去DELPHI中看看行不行，过会发消息！等。。。。。
 
连SQL高手 本站版主Soul都加入进来了
热闹 我喜欢这气氛
大家Go On
 
对啊。用Soul改进后的。
其实这些东西查查SQL Server的帮助基本上能够找到答案。
 
发觉火魂的答案比我简练，应该是对的。
我用大富翁数据库做试验。结果是耗费时间一样都是37秒，我这里数据库可是180万条记录
18万个问题。所以你对字段加索引是非常必须的。
 
我有这样一行代码
var str :string;
str :='along sun along sun thanks a lot sdfkj lkjdsf dlksafj dsaklfjdlksa';
我想把它改成
str :='along sun <---这里要怎么样写才行呀就是程序换行用什么在DELPHI6中
aong sun thanks.........';
多谢英雄
 
火魂的答案的完整化是
SELECT
goods_name,
sum(case when day(bill_date)=1 then
quantity else
0 end) AS '1号',
sum(case when day(bill_date)=2 then
quantity else
0 end) AS '2号',
sum(case when day(bill_date)=3 then
quantity else
0 end) AS '3号',
sum(case when day(bill_date)=4 then
quantity else
0 end) AS '4号',
sum(case when day(bill_date)=5 then
quantity else
0 end) AS '5号',
sum(case when day(bill_date)=6 then
quantity else
0 end) AS '6号',
sum(case when day(bill_date)=7 then
quantity else
0 end) AS '7号',
sum(case when day(bill_date)=8 then
quantity else
0 end) AS '8号',
sum(case when day(bill_date)=9 then
quantity else
0 end) AS '9号',
sum(case when day(bill_date)=10 then
quantity else
0 end) AS '10号',
sum(case when day(bill_date)=11 then
quantity else
0 end) AS '11号',
sum(case when day(bill_date)=12 then
quantity else
0 end) AS '12号',
sum(case when day(bill_date)=13 then
quantity else
0 end) AS '13号',
sum(case when day(bill_date)=14 then
quantity else
0 end) AS '14号',
sum(case when day(bill_date)=15 then
quantity else
0 end) AS '15号',
sum(case when day(bill_date)=16 then
quantity else
0 end) AS '16号',
sum(case when day(bill_date)=17 then
quantity else
0 end) AS '17号',
sum(case when day(bill_date)=18 then
quantity else
0 end) AS '18号',
sum(case when day(bill_date)=19 then
quantity else
0 end) AS '19号',
sum(case when day(bill_date)=20 then
quantity else
0 end) AS '20号',
sum(case when day(bill_date)=21 then
quantity else
0 end) AS '21号',
sum(case when day(bill_date)=22 then
quantity else
0 end) AS '22号',
sum(case when day(bill_date)=23 then
quantity else
0 end) AS '23号',
sum(case when day(bill_date)=24 then
quantity else
0 end) AS '24号',
sum(case when day(bill_date)=25 then
quantity else
0 end) AS '25号',
sum(case when day(bill_date)=26 then
quantity else
0 end) AS '26号',
sum(case when day(bill_date)=27 then
quantity else
0 end) AS '27号',
sum(case when day(bill_date)=28 then
quantity else
0 end) AS '28号',
sum(case when day(bill_date)=29 then
quantity else
0 end) AS '29号',
sum(case when day(bill_date)=30 then
quantity else
0 end) AS '30号',
sum(case when day(bill_date)=31 then
quantity else
0 end) AS '31号'
FROM vdish
where (bill_date >= '2003-02-01') AND (bill_date <= '2003-02-03')
GROUP BY goods_name

你必须对 goods_name bill_date 做索引才对，或者在查询分析中选择索引优化向导，一步
步做就没错。
 
我在改程序，我的项目是广州白去机场的项目，搞了我好久了没有搞定！
我同时也想能认识一些做开发的高手，我几次接了项目，没有人做，真是可惜 了！
。。。等我改完程序我亦放分，英雄慢等！
 
我一般这么写
str :='along sun '#13#10 如果你确定一定需要回车的话。
+'aong sun thanks.........'#13#10
+'.........
 
//hand soul
37秒啊。太漫长了。不知道有没有优化的地方。太久没写SQL语句了。都忘的差不多了。
 
呵呵，我就在广州旁边，中山。
 
嘿嘿。俺就在广州。
 
哦，大富翁数据库啊，这个查询我觉得已经很快了，几乎是遍历表了，所以没法优化了，
但是如果有日期限制就快了。相当于180万条记录做综合统计啊。
 
我不需要回车只是程序太长了不好写我换行写而矣
如：
adoconnection.commandtimeout := 80;
messagebox('along','lskdajflksa;fjk;dsa',
MB_OK);
这是这个意思！
 
吐血。没标识符不能换行。
你总不能这样写吧：
var my
Var := 10;
 
呵呵，都很近啊，下次聚会就可以碰面了。
 
是啊。让我有机会见见传说中的人物们。
 
两眼睛，一鼻子，满脸没有横肉，架副眼镜。
都是一样的。
 
兄弟，增加一个索引 （goods_name,bill_date,quantity）增加效率98%
 
您必须登录或注册才可回复。

Similar threads

S
[转]ClientDataSet加上TDataSetProvider的数据保存问题
回复
0
查看
3K
SUNSTONE的Delphi笔记
S
S
[转]ClientDataSet加上TDataSetProvider的数据保存问题
回复
0
查看
2K
SUNSTONE的Delphi笔记
S
I
一个数列
回复
0
查看
728
import
I
I
一个游戏修改程序 [ 英雄无敌 ]
回复
0
查看
730
import
I
I
一个关于矩阵的算法
回复
0
查看
706
import
I
顶部