时间戳

首页 > 解决方案 > 需要添加 DISTINCT 才能加入

mysql - 需要添加 DISTINCT 才能加入

问题描述

我试图在我的查询中使用 DISTINCT，但我无法获得正确的语法来获得我期望的结果。我做了几个不同的堆栈溢出帖子，我想我终于明白如何包含我需要获得帮助的所有信息。

我在 DB Fiddle 上建立了一个共享空间，所有信息都已在此处加载： https ://www.db-fiddle.com/f/b2o3Wy31hLmwSZLAfpuaVr/0#&togetherjs=IrGzVdtmMh

CREATE TABLE hosts (
  hostid INT NOT NULL,
  name VARCHAR(30) NOT NULL,
  PRIMARY KEY (hostid),
  UNIQUE (name)
);
INSERT INTO hosts 
    (hostid, name) 
VALUES 
    (10761,"CUCM1"),
    (10762,"CUCM2"),
    (10763,"CUCM3");

CREATE TABLE items (
  hostid INT NOT NULL,
  itemid INT NOT NULL,
  name VARCHAR(60) NOT NULL,
  valuemapid INT NOT NULL
);
INSERT INTO items 
    (hostid,itemid,name,valuemapid) 
VALUES 
(10761,304827,"Phone 33 44 55 66 77 88:Model of the Phone",68),
--phone1
(10761,304827,"Phone 33 44 55 66 77 88:Model of the Phone",68), 
--phone1
(10761,304827,"Phone 33 44 55 66 77 88:Model of the Phone",68), 
--phone1
(10761,304828,"Phone 33 44 55 66 88 88:Model of the Phone",68), 
--phone2
(10761,304828,"Phone 33 44 55 66 88 88:Model of the Phone",68), 
--phone2
(10761,304828,"Phone 33 44 55 66 88 88:Model of the Phone",68), 
--phone2
(10761,304829,"Phone 33 44 55 77 77 88:Model of the Phone",68), 
--phone3
(10761,304829,"Phone 33 44 55 77 77 88:Model of the Phone",68), 
--phone3
(10761,304820,"Phone 33 44 44 66 77 88:Model of the Phone",72), 
--phone4
(10761,304820,"Phone 33 44 44 66 77 88:Model of the Phone",72); 
--phone4

CREATE TABLE history_uint (
  itemid INT NOT NULL,
  value INT NOT NULL
);

INSERT INTO history_uint 
    (itemid,value) 
VALUES 
(304827,109),
(304828,109),
(304829,109),
(304820,110);

CREATE TABLE mappings (
  valuemapid INT NOT NULL,
  value INT NOT NULL,
  newvalue VARCHAR (30) NOT NULL,
  PRIMARY KEY (valuemapid)
);

INSERT INTO mappings 
    (valuemapid,value,newvalue) 
VALUES 
(68,109,"Cisco 7841"),
(72,110,"Cisco 7940");

这是我能够拼凑起来的查询

SELECT map.newvalue as 'Model of Phone', Count(*) as 'Number of Phones'
FROM hosts h
    LEFT JOIN items i
        ON h.hostid=i.hostid
    LEFT JOIN history_uint huint
        ON i.itemid=huint.itemid
    LEFT JOIN mappings map
        ON i.valuemapid=map.valuemapid
WHERE huint.value=map.value AND i.name LIKE '%Model of the Phone'
GROUP BY map.newvalue;

现在查询返回

Model of Phone  Number of Phones
Cisco 7841      8
Cisco 7940      2

但我希望在项目表列 itemid 上使用 DISTINCT 所以我们只能返回

Model of Phone  Number of Phones
Cisco 7841      3
Cisco 7940      1

感谢您的帮助！

标签： mysql

解决方案

如果你发生DISTINCT了COUNT什么？

SELECT map.newvalue as 'Model of Phone'
    , Count(DISTINCT i.name) as 'Number of Phones'
FROM hosts h
JOIN items i ON h.hostid = i.hostid
JOIN history_uint huint ON i.itemid = huint.itemid
JOIN mappings map ON i.valuemapid = map.valuemapid AND huint.value=map.value 
WHERE i.name LIKE '%Model of the Phone'
GROUP BY map.newvalue;

推荐阅读