本人写的一个SQL语句 感觉效率太低了 不知还能不能有所优化,希望了解或知道的兄弟帮帮忙 ·~在此谢了
SELECT a.cst_Id, a.cst_no, a.cst_lineno, b.rline_name AS LineName, a.cst_leavetime,
g.car_trade AS cst_carno, c.ch_name AS CartHost, d.ccomp_name AS ComName,
(SELECT COUNT(*)
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1') AS TicketNum,
(SELECT COUNT(*)
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1' AND cart_type = '1') AS AllNum,
(SELECT COUNT(*)
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1' AND cart_type != '1')
AS DiscountNum,
(SELECT cast(SUM(cart_preprice) AS decimal(10, 2))
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1') AS TicketMoney,
(SELECT cast(SUM(cart_preprice) AS decimal(10, 2))
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1' AND cart_type = '1')
AS AllMoney,
(SELECT cast(SUM(cart_preprice) AS decimal(10, 2))
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1' AND cart_type != '1')
AS DiscountMoney,
(SELECT COUNT(*)
FROM t_ticketexaminer_detail e INNER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND (e.Ted_tempID = a.cst_ID)) AS CheckNum,
(SELECT COUNT(*)
FROM t_ticketexaminer_detail e INNER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND cart_type != '1' AND (e.Ted_tempID = a.cst_ID))
AS CheckDiscountNum,
(SELECT COUNT(*)
FROM t_ticketexaminer_detail e INNER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND cart_type = '1' AND (e.Ted_tempID = a.cst_ID))
AS CheckAllNum,
(SELECT SUM(f.cart_preprice)
FROM t_ticketexaminer_detail e LEFT OUTER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND (f.cart_tempID = a.cst_ID)) AS CheckMoney,
(SELECT SUM(f.cart_preprice)
FROM t_ticketexaminer_detail e LEFT OUTER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND cart_type = '1' AND (f.cart_tempID = a.cst_ID))
AS CheckAllMoney,
(SELECT SUM(f.cart_preprice)
FROM t_ticketexaminer_detail e LEFT OUTER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND cart_type != '1' AND (f.cart_tempID = a.cst_ID))
AS CheckDiscountMoney
FROM t_carschemeTemp a INNER JOIN
t_roadline b ON a.cst_lineno = b.rline_no INNER JOIN
t_carhost c ON a.cst_carno = c.ch_no LEFT OUTER JOIN
t_concompany d ON c.ch_concomno = d.ccomp_no INNER JOIN
t_car g ON a.cst_carno = g.car_no
WHERE (a.cst_flag = '1') AND (a.cst_add IN ('1', '0')) AND (a.cst_leavetime BETWEEN
'2007-02-01 00:00:00 ' AND '2007-02-01 23:59:59')
ORDER BY a.cst_leavetime DESC
SELECT a.cst_Id, a.cst_no, a.cst_lineno, b.rline_name AS LineName, a.cst_leavetime,
g.car_trade AS cst_carno, c.ch_name AS CartHost, d.ccomp_name AS ComName,
(SELECT COUNT(*)
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1') AS TicketNum,
(SELECT COUNT(*)
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1' AND cart_type = '1') AS AllNum,
(SELECT COUNT(*)
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1' AND cart_type != '1')
AS DiscountNum,
(SELECT cast(SUM(cart_preprice) AS decimal(10, 2))
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1') AS TicketMoney,
(SELECT cast(SUM(cart_preprice) AS decimal(10, 2))
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1' AND cart_type = '1')
AS AllMoney,
(SELECT cast(SUM(cart_preprice) AS decimal(10, 2))
FROM t_carticket
WHERE cart_tempID = a.cst_ID AND cart_flag = '1' AND cart_type != '1')
AS DiscountMoney,
(SELECT COUNT(*)
FROM t_ticketexaminer_detail e INNER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND (e.Ted_tempID = a.cst_ID)) AS CheckNum,
(SELECT COUNT(*)
FROM t_ticketexaminer_detail e INNER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND cart_type != '1' AND (e.Ted_tempID = a.cst_ID))
AS CheckDiscountNum,
(SELECT COUNT(*)
FROM t_ticketexaminer_detail e INNER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND cart_type = '1' AND (e.Ted_tempID = a.cst_ID))
AS CheckAllNum,
(SELECT SUM(f.cart_preprice)
FROM t_ticketexaminer_detail e LEFT OUTER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND (f.cart_tempID = a.cst_ID)) AS CheckMoney,
(SELECT SUM(f.cart_preprice)
FROM t_ticketexaminer_detail e LEFT OUTER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND cart_type = '1' AND (f.cart_tempID = a.cst_ID))
AS CheckAllMoney,
(SELECT SUM(f.cart_preprice)
FROM t_ticketexaminer_detail e LEFT OUTER JOIN
t_carticket f ON e.ted_invoice = f.cart_invoiceno
WHERE f.cart_flag = '1' AND cart_type != '1' AND (f.cart_tempID = a.cst_ID))
AS CheckDiscountMoney
FROM t_carschemeTemp a INNER JOIN
t_roadline b ON a.cst_lineno = b.rline_no INNER JOIN
t_carhost c ON a.cst_carno = c.ch_no LEFT OUTER JOIN
t_concompany d ON c.ch_concomno = d.ccomp_no INNER JOIN
t_car g ON a.cst_carno = g.car_no
WHERE (a.cst_flag = '1') AND (a.cst_add IN ('1', '0')) AND (a.cst_leavetime BETWEEN
'2007-02-01 00:00:00 ' AND '2007-02-01 23:59:59')
ORDER BY a.cst_leavetime DESC
解决方案 »
- [分享]加速SQL代码输入的利器
- 求写一条排列的查询语句(简单)
- 在建立ODBC数据源时发生的错误
- SQL Server 2005 计划指南
- 用SQL SERVER 2000建立的数据库gg.mdf并导入数据,进入SQL企业管理器中看不到数据?
- 请教类似于QQ好友列表数据表的设计问题
- 用 SQL 怎样为一个已存在的表字段加上一个索引 ? (注意加上索引后,要求不能将原来该列已有的数据删除)
- 请问SQL如何取出相连几笔纪录的平均值呢?
- 在Sql server中,如何设定在某一时刻备份数据库中的数据或进行其他操作?
- 如何实现这样的功能:将多个表格相同定义的字段连接成一个新表,或者生成一个视图?哪种方法更好些?
- 请SQL高手帮忙改个ACCESS查询转SQL
- 请星级大师来帮我看看!!!!!!!!!!!!!!!
所以 也想问问 怎么可以提高效率 如果用临时表在存储关于t_ticketexaminer_detail 的统计
结果 然后在写回不回好一点~