34,836
社区成员
发帖
与我相关
我的任务
分享SQL的一个语法问题
原SQL：
SELECT * FROM A
LEFT JOIN B ON A.ID=B.ID
LEFT JOIN C ON B.TID=C.ID OR B.KID=C.ID ####因效率问题需改成union方式
改成
SELECT * FROM A
LEFT JOIN ON A.ID=B.ID
LEFT JOIN (SELECT * FROM C WHERE B.TID=C.ID UNION SELECT * FROM C WHERE B.KID=C.ID)
不支持这种写法吗，如果我要改成union的方式怎么个写法？
SELECT * FROM A
LEFT JOIN B ON A.ID=B.ID
LEFT JOIN C ON B.TID=C.ID OR B.KID=C.ID ####因效率问题需改成union方式
改成
SELECT * FROM A
LEFT JOIN ON A.ID=B.ID
LEFT JOIN (SELECT * FROM C WHERE B.TID=C.ID UNION SELECT * FROM C WHERE B.KID=C.ID)
不支持这种写法吗，如果我要改成union的方式怎么个写法？
...全文
请发表友善的回复…
发表回复
唐诗三百首 2020-06-19
- 打赏
- 举报
请问原始SQL的应用场景是什么? 如果是一次拿40多万记录到前端做处理可能不太合适? 还是说是其他的什么应用?
sxq129601 2020-06-19
- 打赏
- 举报
不行CPU会100%，之前就是OR导致CPU 100%,改成union就不会，但是数据会不对
唐诗三百首 2020-06-19
- 打赏
- 举报
请试试这样写是否有改善效率,
-- 建索引
create nonclustered index ix_B_VfUID on B(VfUID)
create nonclustered index ix_D_OctObjectGuid on D(OctObjectGuid)
-- 新写法
if object_id('tempdb..#t1') is not null
drop table #t1
select t1.TradeCode,
t1.OrderNO,
t1.CreateTime,
t1.BusinessType,
t1.BuyerID,
t1.SellerID
into #t1
from A t1
where t1.BusinessType in(1,2)
and t1.IsEffective=1
and t1.TradeStatus=4
and t1.CreateTime<getdate()
if object_id('tempdb..#t2') is not null
drop table #t2
select VfUID=convert(nvarchar(50),t2.VfUID),
t2.OctObjectGuid,
t2.VfAdminLoginName
into #t2
from B t2
inner join (select distinct bs=BuyerID from #t1
union
select distinct bs=SellerID from #t1) t1 on convert(nvarchar(50),t2.VfUID)=t1.bs
if object_id('tempdb..#t4') is not null
drop table #t4
select OctObjectGuid=convert(nvarchar(50),t4.OctObjectGuid),
t4.UnBindDate
into #t4
from D t4
inner join (select distinct bs=BuyerID from #t1
union
select distinct bs=SellerID from #t1) t1 on convert(nvarchar(50),t4.OctObjectGuid)=t1.bs
select
t1.TradeCode,
t1.OrderNO,
t1.CreateTime,
payer=case t1.BusinessType
when 1 then isnull(t3.Account,t2.VfAdminLoginName)
when 2 then 'XXX' end,
payee=case t1.BusinessType
when 1 then 'XXX'
when 2 then isnull(t3.Account,t2.VfAdminLoginName) end,
PAYTYPE='payment',
t4.UnBindDate,
TRADE_SRC_VOUCHER_NO=t1.TradeCode
from #t1 t1
left join #t2 t2 on t1.BuyerID=t2.VfUID or t1.SellerID=t2.VfUID
left join C t3 on t2.OctObjectGuid=t3.Guid
left join #t4 t4 on t1.BuyerID=t4.OctObjectGuid or t1.SellerID=t4.OctObjectGuid
sxq129601 2020-06-19
- 打赏
- 举报
原SQL就是这个，只是我把表名改了下而已，大致表结构如下：
CREATE TABLE [dbo].[A] (
[ID] uniqueidentifier NOT NULL,
[OrderNO] nvarchar(50) NULL,
[OrderID] uniqueidentifier NULL,
[TradeStatus] int NULL,
[CreateTime] datetime DEFAULT (getdate()) NULL,
[TradeCode] nvarchar(50) NULL,
[IsEffective] int DEFAULT ((1)) NULL,
[BuyerID] nvarchar(50) NULL,
[SellerID] nvarchar(50) NULL,
[BusinessType] int DEFAULT ((0)) NULL
)
CREATE TABLE [dbo].[B] (
[Guid] uniqueidentifier NOT NULL,
[VfUID] uniqueidentifier NULL,
[OctObjectGuid] uniqueidentifier NULL,
[VfAdminLoginName] nvarchar(50) NULL,
)
CREATE TABLE [dbo].[C] (
[Guid] uniqueidentifier NOT NULL,
[Account] nvarchar(50) NULL,
)
CREATE TABLE [dbo].[D] (
[Guid] uniqueidentifier NOT NULL,
[OctObjectGuid] uniqueidentifier NULL,
[UnBindDate] datetime NULL,
)
唐诗三百首 2020-06-19
- 打赏
- 举报
优化可以从需求入手, 不合理的需求可以调整, 而非一定要让SQL去适应不合理的需求.
唐诗三百首 2020-06-19
- 打赏
- 举报
请问报表一次需显示40万行吗? 假设分页每页100行, 将显示为4000页, 这样合理吗?
sxq129601 2020-06-19
- 打赏
- 举报
是个报表，只是执行的时候CPU100%，经常会CPU告警，看看能否优化下。
XXXXn帅气 2020-06-18
- 打赏
- 举报
我哪分开COUNT了，就最外层一个
[/quote]眼花了 但是好像 union 两个条件都一样呐
sxq129601 2020-06-18
- 打赏
- 举报
我哪分开COUNT了，就最外层一个
XXXXn帅气 2020-06-18
- 打赏
- 举报
感觉你复制了一个 然后 合并一下 而且不能分开COUNT
要在两个合并后个结果集里COUNT
唐诗三百首 2020-06-18
- 打赏
- 举报
6楼是2个问题喔...
sxq129601 2020-06-18
- 打赏
- 举报
【源代码】里的count(1)就是【改后 】子查询里的列，我只是统计下数量而已
唐诗三百首 2020-06-18
- 打赏
- 举报
请把各相关表的表结构(含字段和数据类型)和原SQL语句贴一下(应该不是SELECT count(1) FROM...吧?).
sxq129601 2020-06-17
- 打赏
- 举报
测试出来结果是不一样的。。原来是40多万，改后变80多万
源代码：
SELECT
count(1)
FROM
A t1
LEFT JOIN B t2 ON t1.BuyerID= CONVERT ( VARCHAR ( 100 ), t2.VfUID )
OR t1.sellerid= CONVERT ( VARCHAR ( 100 ), t2.VfUID )
LEFT JOIN C t3 ON t2.OctObjectGuid= t3.Guid
LEFT JOIN D t4 ON t1.BuyerID= CONVERT ( VARCHAR ( 100 ), t4.OctObjectGuid )
OR t1.SellerID= CONVERT ( VARCHAR ( 100 ), t4.OctObjectGuid )
WHERE
t1.BusinessType IN ( 1, 2 )
AND t1.IsEffective= 1
AND t1.TradeStatus= 4
AND t1.CreateTime< getdate( )
改后：
select count(1) from (
SELECT
t1.TradeCode,
t1.OrderNO,
t1.CreateTime,
CASE
t1.BusinessType
WHEN 1 THEN
( CASE WHEN t3.Account IS NULL THEN t2.VfAdminLoginName ELSE t3.Account END )
WHEN 2 THEN
'XXX'
END payer,
CASE
t1.BusinessType
WHEN 1 THEN
'XXX'
WHEN 2 THEN
( CASE WHEN t3.Account IS NULL THEN t2.VfAdminLoginName ELSE t3.Account END )
END payee,
'payment' PAYTYPE,
t4.UnBindDate,
t1.TradeCode AS TRADE_SRC_VOUCHER_NO
FROM
A t1
LEFT JOIN B t2 ON t1.BuyerID= CONVERT ( VARCHAR ( 100 ), t2.VfUID )
LEFT JOIN C t3 ON t2.OctObjectGuid= t3.Guid
LEFT JOIN D t4 ON t1.BuyerID= CONVERT ( VARCHAR ( 100 ), t4.OctObjectGuid )
OR t1.SellerID= CONVERT ( VARCHAR ( 100 ), t4.OctObjectGuid )
WHERE
t1.BusinessType IN ( 1, 2 )
AND t1.IsEffective= 1
AND t1.TradeStatus= 4
AND t1.CreateTime< getdate()
union
SELECT
t1.TradeCode,
t1.OrderNO,
t1.CreateTime,
CASE
t1.BusinessType
WHEN 1 THEN
( CASE WHEN t3.Account IS NULL THEN t2.VfAdminLoginName ELSE t3.Account END )
WHEN 2 THEN
'XXX'
END payer,
CASE
t1.BusinessType
WHEN 1 THEN
'XXX'
WHEN 2 THEN
( CASE WHEN t3.Account IS NULL THEN t2.VfAdminLoginName ELSE t3.Account END )
END payee,
'payment' PAYTYPE,
t4.UnBindDate,
t1.TradeCode AS TRADE_SRC_VOUCHER_NO
FROM
A t1
LEFT JOIN B t2 ON t1.sellerid= CONVERT ( VARCHAR ( 100 ), t2.VfUID )
LEFT JOIN C t3 ON t2.OctObjectGuid= t3.Guid
LEFT JOIN D t4 ON t1.BuyerID= CONVERT ( VARCHAR ( 100 ), t4.OctObjectGuid )
OR t1.SellerID= CONVERT ( VARCHAR ( 100 ), t4.OctObjectGuid )
WHERE
t1.BusinessType IN ( 1, 2 )
AND t1.IsEffective= 1
AND t1.TradeStatus= 4
AND t1.CreateTime< getdate()) as ddd
唐诗三百首 2020-06-17
- 打赏
- 举报
SELECT *
FROM A
LEFT JOIN B ON A.ID=B.ID
LEFT JOIN C ON B.TID=C.ID
union
SELECT *
FROM A
LEFT JOIN B ON A.ID=B.ID
LEFT JOIN C ON B.KID=C.ID
