来自两个具有交集和合并的表的SQL查询，但也具有其他唯一属性

Question

我有两张桌子

表1

Brand  | Price| Shape |weight  |Color |URL
--------------------------------
Philips| 13   | Square| 12lbs  |Blue  |example.com/123
Philips| 4    | Round | 17 lbs |Yellow|example.com/1567

表2

Brand  | Price| Shape  |weight |Color |URL
--------------------------------
Philips| 12   | Square | 12lbs |Blue  |example.com/456
Philips| 4    | Round  | 16 lbs|Yellow|example.com/17987
GE     | 4    | Square | 17 lbs|red   |example.com/17234234

我想编写SQL查询，通过比较最便宜的价格、所有属性和URL，可以从这两个表中选择产品。我试着加入

select  
    case when a.price < b.price then A.price else B.price end as price,
    * 
from 
    Table1 A, table2 B   
where
    A.Brand = B.Brand 
    and A.Shape = B.Shape 
    and A.weight = B.weight 
    and A.color = B.color

但这会返回重复的结果。

我试过使用并和交集，但是它没有给我URL

SELECT  
    Brand , Shape, weight, color, URL 
FROM 
    table1 
WHERE
    Price !='NULL' 
    AND BulbShape != 'null' 
    AND Wattage != 'null' 
    AND Lumens_Initial != 'null' 

UNION

SELECT 
    Brand, Shape, weight, color, URL 
FROM 
    table2  
WHERE 
    Price != 'NULL' 
    AND Shape != 'null' 
    AND weight != 'null' 
    AND color != 'null'

EXCEPT 

SELECT 
    Brand, Shape, weight, color, URL   
FROM 
    table1 
WHERE 
    Price != 'NULL' 
    AND Shape != 'null' 
    AND weight != 'null' 
    AND color != 'null'

INTERSECT 

SELECT 
    Brand, Shape, weight, color, URL 
FROM 
    table2 
WHERE
    Price != 'NULL' 
    AND Shape != 'null' 
    AND Wattage != 'null' 
    AND color != 'null'

我没有任何主键，因为它只是从网上收集的数据。

我如何编写一个查询来获取唯一的数据，包括两个表中的所有列和最小价格？

预期的结果应该是

Brand  | Price| Shape  |weight  |Color  |URL
--------------------------------------------------------------
Philips| 12   | Square | 12 lbs |Blue   |example.com/123
Philips| 4    | Round  | 17 lbs |Yellow |example.com/1567
Philips| 4    | Round  | 16 lbs |Yellow |example.com/17987
GE     | 4    | Square | 17 lbs |red    |example.com/17234234

在第一行中，我刚刚得到的最小价格rest与第一个表保持不变。第二行具有不同的属性，因此我从两个表中获得了行。最后一行只在第二个表中，所以我得到了那一行。

Answer 1

CREATE Procedure joindemo
 as

CREATE TABLE #table1
(
    brand varchar(50),
    price int,
    shape varchar(50),
    weight varchar(50),
    color varchar(50),
    url varchar(100)
    )

CREATE TABLE #table2
(
    brand varchar(50),
    price int,
    shape varchar(50),
    weight varchar(50),
    color varchar(50),
    url varchar(100)
    )


INSERT INTO #table1 VALUES('Philips', 13, 'Square', '12lbs', 'Blue', 'example.com/123')
INSERT INTO #table1 VALUES('Philips', 4, 'Round', '17lbs', 'Yellow', 'example.com/1567')
INSERT INTO #table2 VALUES('Philips', 12, 'Square', '12lbs', 'Blue', 'example.com/456')
INSERT INTO #table2 VALUES('Philips', 4, 'Round', '16lbs', 'Yellow', 'example.com/17987')
INSERT INTO #table2 VALUES('GE', 4, 'Square', '17lbs', 'Red', 'example.com/17234234')

CREATE TABLE #jointable
(
    brand varchar(50),
    price int,
    shape varchar(50),
    weight varchar(50),
    color varchar(50),
    url varchar(100)
    )

INSERT INTO #jointable 
SELECT * FROM #table1
UNION 
SELECT * FROM #table2

SELECT 
j.brand, mp.minprice, j.shape, j.weight, j.color, j.url FROM
(SELECT brand, Min(price) as minprice, shape, weight, color  FROM
#jointable
GROUP BY brand, shape, weight, color) as mp
INNER JOIN #jointable j ON mp.brand = j.brand AND mp.minprice = j.price
AND mp.color = j.color AND mp.shape = j.shape and mp.weight = j.weight

DROP TABLE #table1
DROP TABLE #table2
DROP TABLE #jointable

--exec joindemo;

请注意，您的预期输出是错误的。第一行的网址应该是example.com/456。此外，你还需要决定做什么，如果你得到两个相同的价格！因为您没有指定，所以我无法猜测您是想显示两个还是只显示一个！

Answer 2

这是一个经典的top-n-per-group。

样本数据

DECLARE @table1 TABLE
(
    brand varchar(50),
    price int,
    shape varchar(50),
    weight varchar(50),
    color varchar(50),
    url varchar(100)
);

DECLARE @table2 TABLE
(
    brand varchar(50),
    price int,
    shape varchar(50),
    weight varchar(50),
    color varchar(50),
    url varchar(100)
);

INSERT INTO @table1 (brand,price,shape,weight,color,url) VALUES
('Philips', 13, 'Square', '12lbs', 'Blue', 'example.com/123'),
('Philips', 4, 'Round', '17lbs', 'Yellow', 'example.com/1567');

INSERT INTO @table2 (brand,price,shape,weight,color,url) VALUES
('Philips', 12, 'Square', '12lbs', 'Blue', 'example.com/456'),
('Philips', 4, 'Round', '16lbs', 'Yellow', 'example.com/17987'),
('GE', 4, 'Square', '17lbs', 'Red', 'example.com/17234234');

查询

首先，UNION ALL将两个表转换为CTE_Tables。然后使用ROW_NUMBER为按所有属性划分并按价格(CTE_RN)排序的每一行生成数字。最后，只为每个组选择第一行。

WITH
CTE_Tables
AS
(
    SELECT brand,price,shape,weight,color,url
    FROM @table1

    UNION ALL

    SELECT brand,price,shape,weight,color,url
    FROM @table2
)
,CTE_RN
AS
(
    SELECT brand,price,shape,weight,color,url
        ,ROW_NUMBER() OVER(
            PARTITION BY brand,shape,weight,color
            ORDER BY price) AS rn
    FROM CTE_Tables
)
SELECT brand,price,shape,weight,color,url
FROM CTE_RN
WHERE rn = 1
ORDER BY brand DESC,price DESC,shape DESC,weight DESC,color,url;

结果

+---------+-------+--------+--------+--------+----------------------+
|  brand  | price | shape  | weight | color  |         url          |
+---------+-------+--------+--------+--------+----------------------+
| Philips |    12 | Square | 12lbs  | Blue   | example.com/456      |
| Philips |     4 | Round  | 17lbs  | Yellow | example.com/1567     |
| Philips |     4 | Round  | 16lbs  | Yellow | example.com/17987    |
| GE      |     4 | Square | 17lbs  | Red    | example.com/17234234 |
+---------+-------+--------+--------+--------+----------------------+