不是删除,应该说是选择一部分Key 同时拥有 Type 1 和 0. Key2 因为只有Type 0 所以不需要。
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([Key] [int],[Type] [int],[Count] [int]) INSERT INTO [tb] SELECT '2','0','5' UNION ALL SELECT '17','1','2' UNION ALL SELECT '17','0','14' -->SQL查询如下:SELECT * FROM [tb] t where exists(select 1 from tb where t.[Key]=[KEY] and [TYPE]<>t.[Type]) /* Key Type Count ----------- ----------- ----------- 17 1 2 17 0 14(2 行受影响) */
有没有sql management studio 2008 的code. 我试了一下不过直接0 rows了
Select *, COUNT([Key]) as Tab from ( Select b.InventoryItemKey as [Key], a.axWMSlocationType as [Type], count(a.InventoryLocationKey ) as [SKU COUNT] from axBITransformed.dimInventoryLocation a join axBITransformed.factSOH b on a.InventoryLocationKey = b.InventoryLocationKey and a.axInventLocationID like '3w001' and a.axWMSlocationID not in ('DEF', 'RAW','IN_01') -- Excluded these Location ID and a.axWMSlocationID not like 'Miss%' -- Excluded these Location ID and a.axWMSlocationID not like 'LOC%' -- Excluded these Location ID and a.axWMSlocationID not like 'RECV%' -- Excluded these Location ID and a.axWMSlocationID not like 'AIR%' -- Excluded these Location ID and a.axWMSlocationID not like 'CAN%' -- Excluded these Location ID and a.axWMSlocationID not like 'XF%' -- Excluded these Location ID group by B.[InventoryItemKey], A.axWMSlocationType --order by b.InventoryItemKey ) x where exists(select 1 from x where x.[Key]=[KEY] and [TYPE]<>x.[Type]) Group by [Key], [Type],[SKU COUNT] order by [Key]
我的table 是用了star join 的x. 但是这个join 放在这里不对吗
Select * from ( Select b.InventoryItemKey as [Key], a.axWMSlocationType as [Type], count(a.InventoryLocationKey ) as [SKU COUNT] from axBITransformed.dimInventoryLocation a join axBITransformed.factSOH b on a.InventoryLocationKey = b.InventoryLocationKey and a.axInventLocationID like '3w001' and a.axWMSlocationID not in ('DEF', 'RAW','IN_01') -- Excluded these Location ID and a.axWMSlocationID not like 'Miss%' -- Excluded these Location ID and a.axWMSlocationID not like 'LOC%' -- Excluded these Location ID and a.axWMSlocationID not like 'RECV%' -- Excluded these Location ID and a.axWMSlocationID not like 'AIR%' -- Excluded these Location ID and a.axWMSlocationID not like 'CAN%' -- Excluded these Location ID and a.axWMSlocationID not like 'XF%' -- Excluded these Location ID group by B.[InventoryItemKey], A.axWMSlocationType ) x Group by [Key], [Type],[SKU COUNT] order by [Key]
;with t as ( Select b.InventoryItemKey as [Key], a.axWMSlocationType as [Type], count(a.InventoryLocationKey ) as [SKU COUNT] from axBITransformed.dimInventoryLocation a join axBITransformed.factSOH b on a.InventoryLocationKey = b.InventoryLocationKey and a.axInventLocationID like '3w001' and a.axWMSlocationID not in ('DEF', 'RAW','IN_01') -- Excluded these Location ID and a.axWMSlocationID not like 'Miss%' -- Excluded these Location ID and a.axWMSlocationID not like 'LOC%' -- Excluded these Location ID and a.axWMSlocationID not like 'RECV%' -- Excluded these Location ID and a.axWMSlocationID not like 'AIR%' -- Excluded these Location ID and a.axWMSlocationID not like 'CAN%' -- Excluded these Location ID and a.axWMSlocationID not like 'XF%' -- Excluded these Location ID group by B.[InventoryItemKey], A.axWMSlocationType --order by b.InventoryItemKey ) Select * from t x where exists(select 1 from t where x.[Key]=[KEY] and [TYPE]<>x.[Type])
worked, 感谢,能不能为我解释一下 Select * from t x where exists(select 1 from t where x.[Key]=[KEY] and [TYPE]<>x.[Type]) 这个 t x 怎么来的? 一开始的 ;with t as( 代表什么?
表达不够清楚
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Key] [int],[Type] [int],[Count] [int])
INSERT INTO [tb]
SELECT '2','0','5' UNION ALL
SELECT '17','1','2' UNION ALL
SELECT '17','0','14'
-->SQL查询如下:SELECT * FROM [tb] t where exists(select 1 from tb where t.[Key]=[KEY] and [TYPE]<>t.[Type])
/*
Key Type Count
----------- ----------- -----------
17 1 2
17 0 14(2 行受影响)
*/
我试了一下不过直接0 rows了
Select *, COUNT([Key]) as Tab from
(
Select b.InventoryItemKey as [Key], a.axWMSlocationType as [Type], count(a.InventoryLocationKey ) as [SKU COUNT]
from axBITransformed.dimInventoryLocation a
join axBITransformed.factSOH b
on a.InventoryLocationKey = b.InventoryLocationKey
and a.axInventLocationID like '3w001'
and a.axWMSlocationID not in ('DEF', 'RAW','IN_01') -- Excluded these Location ID
and a.axWMSlocationID not like 'Miss%' -- Excluded these Location ID
and a.axWMSlocationID not like 'LOC%' -- Excluded these Location ID
and a.axWMSlocationID not like 'RECV%' -- Excluded these Location ID
and a.axWMSlocationID not like 'AIR%' -- Excluded these Location ID
and a.axWMSlocationID not like 'CAN%' -- Excluded these Location ID
and a.axWMSlocationID not like 'XF%' -- Excluded these Location ID
group by B.[InventoryItemKey], A.axWMSlocationType
--order by b.InventoryItemKey
) x
where exists(select 1 from x where x.[Key]=[KEY] and [TYPE]<>x.[Type])
Group by [Key], [Type],[SKU COUNT]
order by [Key]
Select * from
(
Select b.InventoryItemKey as [Key], a.axWMSlocationType as [Type], count(a.InventoryLocationKey ) as [SKU COUNT]
from axBITransformed.dimInventoryLocation a
join axBITransformed.factSOH b
on a.InventoryLocationKey = b.InventoryLocationKey
and a.axInventLocationID like '3w001'
and a.axWMSlocationID not in ('DEF', 'RAW','IN_01') -- Excluded these Location ID
and a.axWMSlocationID not like 'Miss%' -- Excluded these Location ID
and a.axWMSlocationID not like 'LOC%' -- Excluded these Location ID
and a.axWMSlocationID not like 'RECV%' -- Excluded these Location ID
and a.axWMSlocationID not like 'AIR%' -- Excluded these Location ID
and a.axWMSlocationID not like 'CAN%' -- Excluded these Location ID
and a.axWMSlocationID not like 'XF%' -- Excluded these Location ID
group by B.[InventoryItemKey], A.axWMSlocationType
) x
Group by [Key], [Type],[SKU COUNT]
order by [Key]
(
Select b.InventoryItemKey as [Key], a.axWMSlocationType as [Type], count(a.InventoryLocationKey ) as [SKU COUNT]
from axBITransformed.dimInventoryLocation a
join axBITransformed.factSOH b
on a.InventoryLocationKey = b.InventoryLocationKey
and a.axInventLocationID like '3w001'
and a.axWMSlocationID not in ('DEF', 'RAW','IN_01') -- Excluded these Location ID
and a.axWMSlocationID not like 'Miss%' -- Excluded these Location ID
and a.axWMSlocationID not like 'LOC%' -- Excluded these Location ID
and a.axWMSlocationID not like 'RECV%' -- Excluded these Location ID
and a.axWMSlocationID not like 'AIR%' -- Excluded these Location ID
and a.axWMSlocationID not like 'CAN%' -- Excluded these Location ID
and a.axWMSlocationID not like 'XF%' -- Excluded these Location ID
group by B.[InventoryItemKey], A.axWMSlocationType
--order by b.InventoryItemKey
)
Select * from t x
where exists(select 1 from t where x.[Key]=[KEY] and [TYPE]<>x.[Type])
Select * from t x
where exists(select 1 from t where x.[Key]=[KEY] and [TYPE]<>x.[Type])
这个 t x 怎么来的?
一开始的 ;with t as( 代表什么?