我的查询编写专业知识已经到了极限。我有下表,其中extid+extdt的组合是一种复合密钥:
ents
entid | extid | extdt | itemid |
=======================================
1000 | 100 | '2016-08-01' | 1 |
1001 | 100 | '2016-08-01' | 2 |
1002 | 200 | '2016-08-01' | 3 |
1003 | 100 | '2016-08-02' | 4 |
1004 | 200 | '2016-08-02' | 5 |
1005 | 100 | '2016-08-02' | 6 |
因此,如果itemid(1或2(在items表中,则查询将返回行1000和1001。如果项目ID 3存在,则返回行1002,依此类推…
items
itemid | itemDesc |
===================
1 | 'fu' |
3 | 'bar' |
4 | 'blah' |
有了上面的项目表,我希望能回来:
entid | extid | extdt | itemid |
=======================================
1000 | 100 | '2016-08-01' | 1 |
1001 | 100 | '2016-08-01' | 2 |
1002 | 200 | '2016-08-01' | 3 |
1003 | 100 | '2016-08-02' | 4 |
1005 | 100 | '2016-08-02' | 6 |
我想不出一个聚合函数能做我想要的事情,ANY/EXISTS似乎也不起作用。我被分组项目ID挂断了。。。有人能告诉我正确的方向吗?
首先,您需要获得与您的物品匹配的复合密钥,但要包括DISTINCT
以避免重复的
SELECT DISTINCT extid, extdt
FROM ents
JOIN items
ON ents.itemid = items.itemid
现在,您可以检索与所选复合密钥匹配的每一行
SELECT *
FROM ents
JOIN ( SELECT DISTINCT extid, extdt
FROM ents
JOIN items
ON ents.itemid = items.itemid
) comp_key
ON ents.extid = comp_key.extid
AND ents.extdt = comp_key.extdt
select *
from ents e1
where e1.extid in
(select extid
from ents e2
where e2.itemid in (select itemid from items))
也许吧?您还可以修改您特定想要的项目ID的最后一个内部查询。
只需根据逻辑将它们连接起来
SELECT e.*
-- records from the ents table
FROM ents e
-- with an extid that matches
JOIN ents extid on e.extid = extid.extid
-- all the records with an itemid in the items table.
JOIN items i on extid.itemid = i.itemid
如果唯一密钥是id和日期,则使用
JOIN ents extid on e.extid = extid.extid and e.extdt = extid.extdt
根据您的描述(但不是示例,这似乎与之相矛盾(:
SELECT e.*
FROM item i
JOIN ent e ON e.itemid = i.itemid
但我怀疑问题不是那么简单吗?
SELECT e.*
FROM [Test].[dbo].[ents] e,[Test].[dbo].[items] i
WHERE e.extid in (SELECT extid from [Test].[dbo].[ents] oe where oe.itemid=i.itemid)
and e.extdt in (SELECT extdt from [Test].[dbo].[ents] oe where oe.itemid=i.itemid)
order by itemid