mysql - 如何解决sql中的重复值
问题描述
我该如何编写这个 sql 查询?结果具有相同的值,我想在下面看到
Hotspot_Adi | Referans_Numarasi | Il | Bolge | CA | ADETI | KYS | ADET
A | xxxx | y | z | AP | 2 | AP | 2
A | xxxx | y | z | Modem | 1 | Modem | 1
但我明白了
Hotspot_Adi | Referans_Numarasi | Il | Bolge | CA | ADETI | KYS | ADET
A | xxxx | y | z | AP | 2 | AP | 2
A | xxxx | y | z | Modem | 1 | Modem | 1
A | xxxx | y | z | Modem | 1 | AP | 2
A | xxxx | y | z | AP | 2 | Modem | 1
我的查询是;
SELECT a.Hotspot_Adi, a.Referans_Numarasi, a.Il, a.Bolge, a.CA, a.ADETI, b.KYS, b.ADET
FROM
(
SELECT t1.Hotspot_Adi, t1.Referans_Numarasi, st.symbol AS Il ,
Bol.sym AS Bolge, t1.Envanter_Sinif AS CA,
Count(t1.Envanter_Sinif) AS ADETI
FROM mdb.dbo.zODYA_EnvanterBilgi AS t1
INNER JOIN mdb.dbo.ca_location ON t1.Referans_Numarasi =mdb.dbo.ca_location.zReferansNo
INNER JOIN mdb.dbo.ca_state_province AS st ON mdb.dbo.ca_location.state = st.id
INNER JOIN mdb.dbo.usp_contact AS t3 ON mdb.dbo.ca_location.zReferansNo = t3.zReferansNo
INNER JOIN mdb.dbo.zODYA_BolgeMudurlukleri AS Bol ON t3.zODYA_BolgeMudurlukleriID = Bol.id
WHERE (t1.Envanter_Sinif like '%Access%'
or
t1.Envanter_Sinif like '%Modem%'
or t1.Envanter_Sinif like '%Switch%'
)
GROUP BY t1.Hotspot_Adi, t1.Envanter_Sinif, t1.Referans_Numarasi, st.symbol, Bol.sym
)AS a
INNER JOIN
(
SELECT t5.Hotspot_Adi, t5.Referans_Numarasi, t5.Envanter_Sinif AS KYS,
Count(t5.Envanter_Sinif) AS ADET
FROM mdb.dbo.zODYA_EnvanterBilgi AS t5
INNER JOIN NetPerfMon.dbo.Nodes AS t2 ON t5.Seri_Numarasi = t2.Serial_Number
WHERE (t5.Envanter_Sinif like '%Access%'
or
t5.Envanter_Sinif like '%Modem%'
or t5.Envanter_Sinif like '%Switch%'
)
GROUP BY t5.Hotspot_Adi, t5.Referans_Numarasi, t5.Envanter_Sinif
) AS b
ON a.Referans_Numarasi = b.Referans_Numarasi
我应该怎么做?谢谢你。
解决方案
仅使用Referans_Numarasi
您有部分笛卡尔积。
您需要完成您的加入:a.CA and b.KYS
SELECT
a.Hotspot_Adi,
a.Referans_Numarasi,
a.Il,
a.Bolge,
a.CA,
a.ADETI,
b.KYS,
b.ADET
FROM
(
SELECT
t1.Hotspot_Adi, t1.Referans_Numarasi, st.symbol AS Il , Bol.sym AS Bolge, t1.Envanter_Sinif AS CA, Count(t1.Envanter_Sinif) AS ADETI
FROM
mdb.dbo.zODYA_EnvanterBilgi AS t1
INNER JOIN mdb.dbo.ca_location ON t1.Referans_Numarasi = mdb.dbo.ca_location.zReferansNo
INNER JOIN mdb.dbo.ca_state_province AS st ON mdb.dbo.ca_location.state = st.id
INNER JOIN mdb.dbo.usp_contact AS t3 ON mdb.dbo.ca_location.zReferansNo = t3.zReferansNo
INNER JOIN mdb.dbo.zODYA_BolgeMudurlukleri AS Bol ON t3.zODYA_BolgeMudurlukleriID = Bol.id
WHERE
(t1.Envanter_Sinif like '%Access%' or t1.Envanter_Sinif like '%Modem%' or t1.Envanter_Sinif like '%Switch%')
GROUP BY
t1.Hotspot_Adi, t1.Envanter_Sinif, t1.Referans_Numarasi, st.symbol, Bol.sym
) AS a
INNER JOIN
(
SELECT
t5.Hotspot_Adi, t5.Referans_Numarasi, t5.Envanter_Sinif AS KYS, Count(t5.Envanter_Sinif) AS ADET
FROM
mdb.dbo.zODYA_EnvanterBilgi AS t5
INNER JOIN NetPerfMon.dbo.Nodes AS t2 ON t5.Seri_Numarasi = t2.Serial_Number
WHERE
(t5.Envanter_Sinif like '%Access%' or t5.Envanter_Sinif like '%Modem%' or t5.Envanter_Sinif like '%Switch%')
GROUP BY
t5.Hotspot_Adi, t5.Referans_Numarasi, t5.Envanter_Sinif
) AS b
ON
a.Referans_Numarasi = b.Referans_Numarasi and a.CA and b.KYS
推荐阅读
- python - Pandas 列表的列,检查列表是否相交
- javascript - 更改事件的多边形选项。谷歌地图
- javascript - CSS 3D 动画车轮偏离中心
- generics - 为什么这一生不会“过期”?
- java - 在java中生成特定随机数的概率
- javascript - 如果 importKey() 已经有了,为什么还要在 decrypt() 中定义算法?
- c# - dnlib - 如何更改应用程序 .NetFramework 版本
- android - 使这种布局适应方向变化的最简单方法是什么?
- mysql - 在一对多表之间创建 MySQL 视图
- unity3d - 如何从统一导出到搅拌机?