在我的表中INVENTORY
有3列varchar
代码,版本,描述。例如
code || version || description
--------------------------
AS100 || 01 || description
AS100 || 02 || description
AS100 || 02 || description
AS100 || 02 || description
AS200 || 01 || description
AS300 || 01 || description
AS300 || 01 || description
我想选择所有具有相同产品和多个版本的产品。在上表中,此查询将返回:
AS100
- 01
- description
AS100
- 02
-description
我尝试使用HAVING
语句,但没有得到以上结果。
例如,我尝试了此操作:
SELECT code, version, Count(*) FROM INVENTORY
GROUP BY version
HAVING Count(*) =1
但它会返回出现一次的所有代码。
我在这里创建了一个SQLFIDDLE(我希望它可以播放,有时页面会冻结)我的数据库的一个小转储
CREATE TABLE "INVENTORY" (
"code" VARCHAR,
"version" VARCHAR,
"description" VARCHAR
)
INSERT INTO INVENTORY (code, version, description) VALUES("AS100", "01", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS100", "02", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS100", "02", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS100", "02", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS200", "02", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS300", "01", "description");
INSERT INTO INVENTORY (code, version, description) VALUES("AS300", "01", "description");
也许这就是您想要的?
select *, count(*) from (select * from INVENTORY group by code, version)
group by code
having count(*)>1
也就是说,首先创建一个由感兴趣的两列汇总的表,然后从该表中选择行,其中至少存在两个不同的code值。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句