主页 / dba / 问题 / 242319
Accepted
Asrar Ahmad Ehsan
Asked: 2019-07-09 00:30:21 +0800 CST

SQL Server 中表的记录排序

  • 772

我有以下表格：

Customer (ID, Name, CountryID)

Country (ID, Name, Code)

从 Web 应用程序中，用户选择数量，Customers然后Countries系统根据这些参数显示结果。

例如，用户选择客户数量2和国家US，UK然后系统必须显示结果如下：

CustomerID    CustomerName    Country
--------------------------------------
1             A               US
2             B               US
3             C               UK
4             D               UK
.
.
.

我们怎样才能达到上述结果？

sql-server t-sql

3 个回答

  1. Best Answer

    测试

    WITH
cte1 AS ( SELECT 'US' country, 10 cnt
          UNION ALL
          SELECT 'UK', 2
          -- UNION ALL
          -- ... 
        ),
cte2 AS ( SELECT customer.*, 
                 country.name country, 
                 ROW_NUMBER() OVER (PARTITION BY country.name 
                                    ORDER BY customer.id) rn
          FROM customer
          JOIN country on customer.country_id = country.id 
        )
SELECT cte2.id CustomerID, cte2.name CustomerName, cte2.Country
FROM cte2
JOIN cte1 ON cte1.country = cte2.country
WHERE cte2.rn <= cte1.cnt
    • 1

  2. 最好的选择之一是通过 UNION ALL：

    SELECT TOP(2) c.ID AS [CustomerID], c.[Name] AS [CustomerName], cc.Code AS [Country]
FROM Customer AS c
INNER JOIN Country AS cc ON c.CountryID=cc.ID
WHERE cc.Code='US'
UNION ALL
SELECT TOP(2) c.ID AS [CustomerID], c.[Name] AS [CustomerName], cc.Code AS [Country]
FROM Customer AS c
INNER JOIN Country AS cc ON c.CountryID=cc.ID
WHERE cc.Code='UK';

    如有必要，您可以动态生成代码

    • 0

  3. 试试这个

    --Number of Customer
DECLARE @NumerOfCustomer INT=2
--you need to receive the countrys list
DECLARE @Countrys NVARCHAR(MAX)='1,2'

--Simulated Table
DECLARE @Customer TABLE (ID INT, Name NVARCHAR(50), CountryID INT)
DECLARE @Country TABLE (ID INT, Name NVARCHAR(50), Code INT)

--INSERT Customer
INSERT INTO @Customer VALUES(1,'Test1',1)
INSERT INTO @Customer VALUES(2,'Test2',1)
INSERT INTO @Customer VALUES(3,'Test3',1)
INSERT INTO @Customer VALUES(4,'Test4',2)
INSERT INTO @Customer VALUES(5,'Test5',2)
INSERT INTO @Customer VALUES(6,'Test6',2)
INSERT INTO @Customer VALUES(7,'Test7',3)
INSERT INTO @Customer VALUES(8,'Test8',3)

--INSERT Country
INSERT INTO @Country VALUES(1,'UK',1)
INSERT INTO @Country VALUES(2,'ES',2)
INSERT INTO @Country VALUES(3,'MX',1)

--How many countries are received by means of a function Split
DECLARE @Count INT=(SELECT COUNT(*) FROM dbo.Split(@Countrys,','))

--We insert the result here
DECLARE @TableAux TABLE(CustomerID INT,CustomerName NVARCHAR(50),Country NVARCHAR(50))
--This is a Aux count
DECLARE @Help INT=1
--The Country Aux
DECLARE @CountryAux NVARCHAR(10)
WHILE @Help <=@Count
BEGIN

SELECT @CountryAux=(SELECT Data FROM dbo.Split(@Countrys,',') WHERE ID=@Help)

INSERT INTO @TableAux
SELECT TOP (@NumerOfCustomer)  CR.ID AS CustomerID,CR.[Name] AS CustomerName,CT.Name AS Country
FROM @Customer CR
INNER JOIN @Country CT ON CT.ID=CR.CountryID
WHERE CT.ID=@CountryAux

SET @Help=@Help+1
--RESET 
SET @CountryAux=''
END


--Select ALL Result
SELECT * FROM @TableAux

    如果你收到的国家怎么串然后

    --Number of Customer
DECLARE @NumerOfCustomer INT=2
--you need to receive the countrys list
--
DECLARE @Countrys NVARCHAR(MAX)='UK,ES'

--Simulated Table
DECLARE @Customer TABLE (ID INT, Name NVARCHAR(50), CountryID INT)
DECLARE @Country TABLE (ID INT, Name NVARCHAR(50), Code INT)

--INSERT Customer
INSERT INTO @Customer VALUES(1,'Test1',1)
INSERT INTO @Customer VALUES(2,'Test2',1)
INSERT INTO @Customer VALUES(3,'Test3',1)
INSERT INTO @Customer VALUES(4,'Test4',2)
INSERT INTO @Customer VALUES(5,'Test5',2)
INSERT INTO @Customer VALUES(6,'Test6',2)
INSERT INTO @Customer VALUES(7,'Test7',3)
INSERT INTO @Customer VALUES(8,'Test8',3)

--INSERT Country
INSERT INTO @Country VALUES(1,'UK',1)
INSERT INTO @Country VALUES(2,'ES',2)
INSERT INTO @Country VALUES(3,'MX',1)

--How many countries are received by means of a function Split
DECLARE @Count INT=(SELECT COUNT(*) FROM dbo.Split(@Countrys,','))

--We insert the result here
DECLARE @TableAux TABLE(CustomerID INT,CustomerName NVARCHAR(50),Country NVARCHAR(50))
--This is a Aux count
DECLARE @Help INT=1
--The Country Aux
DECLARE @CountryAux NVARCHAR(10)
WHILE @Help <=@Count
BEGIN
SELECT @CountryAux=(SELECT Data FROM dbo.Split(@Countrys,',') WHERE ID=@Help)

INSERT INTO @TableAux
SELECT TOP (@NumerOfCustomer)  CR.ID AS CustomerID,CR.[Name] AS CustomerName,CT.Name AS Country
FROM @Customer CR
INNER JOIN @Country CT ON CT.ID=CR.CountryID

--if you receive the string then use CT.Name
WHERE CT.Name=@CountryAux

SET @Help=@Help+1
--RESET 
SET @CountryAux=''
END


--Select ALL Result
SELECT * FROM @TableAux

    分割功能

    CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
WITH Split(stpos,endpos) 
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
    • 0

相关问题