时间戳

首页 > 解决方案 > SQL 查询 - 合并具有相同值的两种类型的对象

sql - SQL 查询 - 合并具有相同值的两种类型的对象

问题描述

我想找到具有相同速度和内存的 PC 型号对。这些对仅列出一次。这是我的数据和期望的结果：

我确实得到了结果，但是查询太长了，我知道有更短的方法。希望大家帮助我。

这是我的查询

DECLARE @FOR INT = 1
DECLARE @SPEED INT
DECLARE @RAM INT
DECLARE @MODEL INT
DECLARE @LIST TABLE(SPEED INT, RAM INT)
DECLARE @LISTMODEL TABLE(MODEL INT)
DECLARE @RESULT TABLE(PC1 INT, PC2 INT)
DECLARE @RESULTREAL TABLE(COUPLE NVARCHAR(20), SPEED INT, RAM INT)
DECLARE @COUNT INT
    WHILE(1=1)
    BEGIN
        IF(NOT EXISTS(SELECT TOP(1) SPEED FROM @LIST))
        BEGIN
            INSERT @LIST(SPEED,RAM)
            SELECT speed,ram
            FROM VW_count
        END
        BREAK
    END
    SET @COUNT = (SELECT COUNT(SPEED) FROM @LIST) 
    WHILE @FOR <= @COUNT
    BEGIN
        SET @SPEED = (SELECT KETQUA.SPEED FROM (SELECT ROW_NUMBER() OVER (ORDER BY SPEED) AS STT, SPEED FROM @LIST) AS KETQUA WHERE KETQUA.STT = 1)
        SET @RAM = (SELECT KETQUA.RAM FROM (SELECT ROW_NUMBER() OVER (ORDER BY RAM) AS STT, RAM FROM @LIST) AS KETQUA WHERE KETQUA.STT = 1)
        IF @SPEED IS NULL
        BEGIN
            BREAK
        END
        ELSE
            BEGIN
                IF(EXISTS(SELECT speed FROM PC WHERE speed = @SPEED AND ram = @RAM))
                BEGIN
                    INSERT @LISTMODEL(MODEL)
                    SELECT model FROM PC WHERE speed = @SPEED AND ram = @RAM
                    INSERT @RESULT(PC1,PC2)
                    SELECT DISTINCT L1.MODEL, L2.MODEL FROM @LISTMODEL AS L1 , @LISTMODEL AS L2
                    INSERT @RESULTREAL(COUPLE,SPEED,RAM)
                    SELECT CONCAT(R1.PC1, ', ', R1.PC2), @SPEED, @RAM FROM @RESULT R1 WHERE R1.PC1 > R1.PC2 OR NOT EXISTS (SELECT * FROM @RESULT R2 WHERE R2.PC1 = R1.PC2 AND R1.PC2 = R2.PC1)
                END
                DELETE @RESULT
                DELETE @LISTMODEL
            END
    SET @FOR = @FOR + 1
    DELETE TOP(1) FROM @LIST 
    CONTINUE
    END
    SELECT * FROM @RESULTREAL

标签： sqlsql-servertsql

解决方案

当您还可以使用基于集合的逻辑时，使用过程语句通常是个坏主意。

[PC]您可以根据[speed]and[ram]字段的值对表使用自联接进行 SELECT 查询，但[model]第二个表的值大于第一个表的值。像这样的东西：

DECLARE @RESULTREAL TABLE(COUPLE NVARCHAR(20), SPEED INT, RAM INT);

INSERT INTO @RESULTREAL
SELECT
  CAST(T1.[model] AS NVARCHAR) + N', ' + CAST(T2.[model] AS NVARCHAR),
  T1.[speed],
  T1.[ram]
FROM
  [PC] AS T1
  INNER JOIN [PC] AS T2 ON
    T2.[speed] = T1.[speed] AND
    T2.[ram] = T1.[ram]
WHERE
  T2.[model] > T1.[model];

推荐阅读