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

交叉表试过了吗？

查到了，但是超 时呀大英雄，怎么办！

我不会用交叉表，有什么新办法吗？
TO：轻松虎，，大侠不行呀，再看看是怎么回来呀！
TO：shbjkl, ，为什么没有数据呀！可是我的1号的2号的有东西呀！

目前有点意思了你们看
SELECT a.goods_name, SUM(b.total) AS '1号', SUM(c.total) AS '2号'
FROM VDish a LEFT OUTER JOIN
(SELECT goods_name, total
FROM vdish
WHERE day(bill_date) = 1) b ON
a.goods_name = b.goods_name LEFT OUTER JOIN
(SELECT goods_name, total
FROM vdish
WHERE day(bill_date) = 2) c ON a.goods_name = c.goods_name
WHERE (a.bill_date < '2003-2-15') AND (a.bill_date > '2003-2-1')
GROUP BY a.goods_name看看这个行吗？出来了出来了，过会我给大家派分，，，今晚请客，有没有
广州的朋友！！！！！！！！

最好不要left join 层数 太多
我以前写过一个项目 ， 23层 是 速度奇快 但是24层 就要一分钟
可是这个项目 中 有些 报表 要 40多层
最后我改成了
1：临时表
2：ADODataSet1.CreateDataSet
3：数据放到stringgrid里处理
三种方式

各位英雄，，，不好意思，，
我有31个JOIN，我的超时了，怎么办呀！
我已经把SQL SERVER 2000数据库的查询时间由600秒改为0（无限制）
我在ADOCONNECTION中也把 CONNECTION TIME 设置为80000秒
COMMANDTIME OUT 设置为10000秒，但不管怎么样还是说超时
！！！！
大侠，怎么办呀！

我试了几次了，全是在18-20秒时超时的，我哪里能让它不会超时呢？各位大侠！救我呀！

我是这样写的SQL
SELECT a.goods_name, SUM(b.quantity) AS '1号', SUM(c.quantity) AS '2号', SUM(d.quantity)
AS '3号', SUM(e.quantity) AS '4号', SUM(f.quantity) AS '5号', SUM(g.quantity) AS '6号',
SUM(h.quantity) AS '7号', SUM(i.quantity) AS '8号', SUM(j.quantity) AS '9号',
SUM(k.quantity) AS '10号', SUM(l.quantity) AS '11号', SUM(m.quantity) AS '12号',
SUM(n.quantity) AS '13号', SUM(o.quantity) AS '14号', SUM(p.quantity) AS '15号',
SUM(q.quantity) AS '16号', SUM(r.quantity) AS '17号', SUM(s.quantity) AS '18号',
SUM(t.quantity) AS '19号', SUM(u.quantity) AS '20号', SUM(v.quantity) AS '21号',
SUM(w.quantity) AS '22号', SUM(x.quantity) AS '23号', SUM(y.quantity) AS '24号',
SUM(z.quantity) AS '25号', SUM(z1.quantity) AS '26号', SUM(z2.quantity) AS '27号',
SUM(z3.quantity) AS '28号', SUM(z4.quantity) AS '29号', SUM(z5.quantity) AS '30号',
SUM(z6.quantity) AS '31号'
FROM VDish a LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 1) b ON
a.goods_name = b.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 2) c ON
a.goods_name = c.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 3) d ON
a.goods_name = d.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 4) e ON
a.goods_name = e.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 5) f ON a.goods_name = f.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 6) g ON
a.goods_name = g.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 7) h ON
a.goods_name = h.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 8) i ON a.goods_name = i.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 9) j ON a.goods_name = j.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 10) k ON
a.goods_name = k.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 11) l ON
a.goods_name = l.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 12) m ON
a.goods_name = m.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 13) n ON
a.goods_name = n.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 14) o ON
a.goods_name = o.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 15) p ON
a.goods_name = p.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 16) q ON
a.goods_name = q.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 17) r ON
a.goods_name = r.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 18) s ON
a.goods_name = s.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 19) t ON
a.goods_name = t.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 20) u ON
a.goods_name = u.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 21) v ON
a.goods_name = v.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 22) w ON
a.goods_name = w.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 23) x ON
a.goods_name = x.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 24) y ON
a.goods_name = y.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 25) z ON
a.goods_name = z.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 26) z1 ON
a.goods_name = z1.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 27) z2 ON
a.goods_name = z2.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 28) z3 ON
a.goods_name = z3.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 29) z4 ON
a.goods_name = z4.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 30) z5 ON
a.goods_name = z5.goods_name LEFT OUTER JOIN
(SELECT goods_name, quantity
FROM vdish
WHERE day(bill_date) = 31) z6 ON a.goods_name = z6.goods_name
WHERE (a.bill_date >= '2003-02-01') AND (a.bill_date <= '2003-02-03')
GROUP BY a.goods_name
可是我看不哪里有问题呀，哪里都超时，真的没有办法！

我一共才有13450条记录而矣呀，怎么这么慢呀！

结过测试 到4 号就已经不行了，不知道大家有没有什么新办法，

大侠？？？怎么办呀

to : hfghfghfg, 我的真是超时呀，不好办了，，，帮助我了好吗？
多谢！

试一下这个看看行不行。
创建一个临时表t_temp
t_temp是根据day来汇总的表。
假设你的表为t_atable
CREATE TABLE t_atable(tid int, tdate datetime, tnumber int);
--
CREATE TABLE t_temp(tid int, tday int, ttotal int);
DELETE FROM t_temp;
INSERT INTO t_temp(tid, tday, ttotal) SELECT tid, day(tdate), SUM(tnumber)
FROM t_atable GROUP BY tid, day(tdate);
SELECT tid,
SUM(CASE tday WHEN 1 then
ttotal else
0 END) AS '1号',
SUM(CASE tday WHEN 2 then
ttotal else
0 END) AS '2号',
SUM(CASE tday WHEN 3 then
ttotal else
0 END) AS '3号',
SUM(CASE tday WHEN 4 then
ttotal else
0 END) AS '4号'
FROM t_temp
GROUP BY tid

这种报表十分简单啊，做一个父子表，在STRINGGRID逐行中按父表记录输出子表的所有记录，
就可生成，在用可以直接打印STRINGGRID内容的报表控件打印就可以了。[]

To: 一洲的阿畅,能不能说详细点，目前我只用那个LEFT JOIN 实现了 1号和2号的数据，
如果加到4号，定是超时，真是好难办，有人说要建立索引，，有用吗？

请哪位英雄 说说怎么样给我的数据库建立索引
tmbill_gen (主表）
bill_id bill_date total,...
tmbill_det (从表，明细）
bill_id goods_id ......
我要查数据亦要先从这两个表中查起才行！
是不是那个 bill_id 亦可以做为索引还是怎么样的！

ReportMachine真牛
这种报表也制作得出来
http://delphi2002.y365.com/images/rm3.gif
你看看是就是你要的效果

用SQL不能实现吗？一定要用控件吗？

开始我也建议用SQL
但那么多语句累不累啊
今天看到了这个交叉表可以实现你要的效果
所以给个线索了
慢慢研究,这个问题太经典了
大功告成,别忘了大家一起分享

SELECT goods_name,
SUM(CASE day(bill_date) WHEN 1 then
quantity else
0 END) AS '1号',
SUM(CASE day(bill_date) WHEN 2 then
quantity else
0 END) AS '2号',
SUM(CASE day(bill_date) WHEN 3 then
quantity else
0 END) AS '3号',
SUM(CASE day(bill_date) WHEN 4 then
quantity else
0 END) AS '4号',
SUM(CASE day(bill_date) WHEN 5 then
quantity else
0 END) AS '5号',
SUM(CASE day(bill_date) WHEN 6 then
quantity else
0 END) AS '6号'
-- ... 到30号
FROM vdish
GROUP BY goods_name
--看看有什么东西可以出来。