请教大家一个问题,
MySql中的UNION ALL语句连接的查询语句,如果条件相同,能否提成一个条件。
比如:
select id,name,count where tbl1 where id='01'
union all
select id,name,count where tbl1 where id='01'
union all
select id,name,count where tbl1 where id='01'上面3条语句的where条件都是id='01',能不能写成一个,不用写3遍。多谢了~
MySql中的UNION ALL语句连接的查询语句,如果条件相同,能否提成一个条件。
比如:
select id,name,count where tbl1 where id='01'
union all
select id,name,count where tbl1 where id='01'
union all
select id,name,count where tbl1 where id='01'上面3条语句的where条件都是id='01',能不能写成一个,不用写3遍。多谢了~
select id,name,count where tbl1
union all
select id,name,count where tbl1
union all
select id,name,count where tbl1 ) a
where id='01'
具体的语句很复杂,上面的例子可以代替。
按照上面的语句,可以查出3条一样的记录存放的一个数据集中,
但需要写3个where条件,我就是想用一个where语句代替,但不知道怎么写。
比如写成这样(我知道不对,但想成这样)
(select id,name,count where tbl1
union all
select id,name,count where tbl1
union all
select id,name,count where tbl1)
where id='01'
FROM (
SELECT C.Store_Name,B.Item_ID,E.Info_Name,F.Catalog_ID,F.NAME,if(D.Price<>'',D.Price,'时价') AS Price,
SUM(B.Quantity) AS Quantity,SUM(B.Total_Price) AS Total_Price
FROM OrderMstTBL A , OrderInfoTBL B , StoreMstTBL C,ItemMstTBL D,ItemNameTBL E,
CatalogsTbl F
WHERE A.Order_ID=B.Order_ID AND A.Del_Flg='0' AND A.Store_ID=C.Store_ID
AND B.Item_ID=D.Item_ID AND D.Item_ID=E.Item_ID AND E.Lang_ID='zh'
AND D.Catalog_ID=F.Catalog_ID AND F.Lang_ID='zh'
GROUP BY B.Item_ID
UNION ALL
SELECT '类别小计','99999998','',Catalog_ID,NAME,'',SUM(Quantity),SUM(Total_Price)
FROM (
SELECT C.Store_Name,B.Item_ID,E.Info_Name,F.Catalog_ID,F.NAME,D.Price,
SUM(B.Quantity) AS Quantity,SUM(B.Total_Price) AS Total_Price
FROM OrderMstTBL A , OrderInfoTBL B , StoreMstTBL C,ItemMstTBL D,ItemNameTBL E,
CatalogsTbl F
WHERE A.Order_ID=B.Order_ID AND A.Del_Flg='0' AND A.Store_ID=C.Store_ID
AND B.Item_ID=D.Item_ID AND D.Item_ID=E.Item_ID AND E.Lang_ID='zh'
AND D.Catalog_ID=F.Catalog_ID AND F.Lang_ID='zh'
GROUP BY B.Item_ID) AS ITEMS_TBL
GROUP BY NAME
UNION ALL
SELECT '总计',99999999,'','99999999','','',SUM(B.Quantity),SUM(B.Total_Price)
FROM OrderMstTBL A , OrderInfoTBL B , StoreMstTBL C,ItemMstTBL D,ItemNameTBL E,
CatalogsTbl F
WHERE A.Order_ID=B.Order_ID AND A.Del_Flg='0' AND A.Store_ID=C.Store_ID
AND B.Item_ID=D.Item_ID AND D.Item_ID=E.Item_ID AND E.Lang_ID='zh'
AND D.Catalog_ID=F.Catalog_ID AND F.Lang_ID='zh') AS KIND_TBL
ORDER BY Catalog_ID,Item_ID ASC红色的where都一样,能否写成一个?还请帮忙看看
SELECT C.Store_Name,B.Item_ID,E.Info_Name,F.Catalog_ID,F.NAME,D.Price,
SUM(B.Quantity) AS Quantity,SUM(B.Total_Price) AS Total_Price
FROM OrderMstTBL A , OrderInfoTBL B , StoreMstTBL C,ItemMstTBL D,ItemNameTBL E,
CatalogsTbl F
WHERE A.Order_ID=B.Order_ID AND A.Del_Flg='0' AND A.Store_ID=C.Store_ID
AND B.Item_ID=D.Item_ID AND D.Item_ID=E.Item_ID AND E.Lang_ID='zh'
AND D.Catalog_ID=F.Catalog_ID AND F.Lang_ID='zh'
GROUP BY B.Item_ID存为VIEW,再调用即可
真不能写在一起吗?
SUM(B.Quantity) AS Quantity,SUM(B.Total_Price) AS Total_Price
FROM OrderMstTBL A , OrderInfoTBL B , StoreMstTBL C,ItemMstTBL D,ItemNameTBL E,
CatalogsTbl F
WHERE A.Order_ID=B.Order_ID AND A.Del_Flg='0' AND A.Store_ID=C.Store_ID
AND B.Item_ID=D.Item_ID AND D.Item_ID=E.Item_ID AND E.Lang_ID='zh'
AND D.Catalog_ID=F.Catalog_ID AND F.Lang_ID='zh'
GROUP BY B.Item_ID with rollup
试试
SELECT C.Store_Name,B.Item_ID,E.Info_Name,F.Catalog_ID,F.NAME,if(D.Price<>'',D.Price,'时价') AS Price,
SUM(B.Quantity) AS Quantity,SUM(B.Total_Price) AS Total_Price
FROM OrderMstTBL A , OrderInfoTBL B , StoreMstTBL C,ItemMstTBL D,ItemNameTBL E,
CatalogsTbl F
WHERE A.Order_ID=B.Order_ID AND A.Del_Flg='0' AND A.Store_ID=C.Store_ID
AND B.Item_ID=D.Item_ID AND D.Item_ID=E.Item_ID AND E.Lang_ID='zh'
AND D.Catalog_ID=F.Catalog_ID AND F.Lang_ID='zh'
GROUP BY B.Item_ID WITH ROLLUP
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+-------------+
已经在JAVA端做了一部分,问题解决了,
虽然没能完全在SQL端解决,但也可以了。
谢谢2位,分我平均分啦 呵呵