时间戳

首页 > 解决方案 > 查询结果的每条记录/行一列

sql-server - 查询结果的每条记录/行一列

问题描述

我有一个数据库，其中的零件存储在“零件”表中。每个零件都经过一定的测量（以确定其直径），该测量存储在“测量”表中。测量使用存储在“规则集”表中的特定规则集。这些规则用于评估零件的类别（它可能匹配多个类别）。所以有一个交叉表“Ruleset_x_Category”，它的主键是外键“ID_Ruleset”和“Category”的组合，以确保这个组合是唯一的（每个规则集可以与每个类别唯一的组合）进一步连接规则本身包含一个 Diameter_min- 和 Diameter_max-Value 以检查零件是否与类别匹配。每个规则集的规则数量各不相同。

在此处输入图像描述

工作流程：你有一个零件，你测量它，你选择一个测量规则集，然后你应该得到一个列表，告诉你零件是否适合多个类别。

其中大部分是直截了当的。查询规则，如果测量值介于最小值/最大值之间，则获得“是/否”等。对我来说不容易的是，如果我查询（可变数量的）规则，则为我得到的每一行获取一个新列。这可能吗？如果是这样，我使用的技术是什么。有没有更好的方法来安排我的桌子？

 SELECT  Measurement.id_part, Measurement.diameter, Category.Name, IIF(Measurement.diameter BETWEEN [Rule].Diameter_min AND [Rule].Diameter_max, 'Yes', 'No') As HitsCategory
 FROM Measurement INNER JOIN
                      RuleSet ON Measurement.id_ruleset = RuleSet.ID INNER JOIN
                      RuleSet_x_Category ON RuleSet.ID = RuleSet_x_Category.Id_RuleSet INNER JOIN
                      [Rule] ON RuleSet_x_Category.id_rule = [Rule].Id INNER JOIN
                      Category ON RuleSet_x_Category.id_category = Category.Id

这就是我这个：

在此处输入图像描述

我想要的是这样的：

在此处输入图像描述

Larnu 给了我使用 pivot 关键字的提示。我找到了这篇文章，但是，我很难将它应用到我的 IIF 声明中。我试过的：

 DECLARE 
     @columns NVARCHAR(MAX) = '', 
     @sql     NVARCHAR(MAX) = '';

 -- select the category names
 SELECT 
     @columns+=QUOTENAME([Name]) + ','
 FROM 
     Category
 ORDER BY 
     [Name];

 -- remove the last comma
 SET @columns = LEFT(@columns, LEN(@columns) - 1);

 -- construct dynamic SQL
 SET @sql ='
 SELECT * FROM   
 (
 SELECT Measurement.id, Measurement.id_part, Measurement.diameter, RuleSet.ID, Category.Name
 FROM Measurement INNER JOIN
                      RuleSet ON Measurement.id_ruleset = RuleSet.ID INNER JOIN
                      RuleSet_x_Category ON RuleSet.ID = RuleSet_x_Category.Id_RuleSet INNER JOIN
                      [Rule] ON RuleSet_x_Category.id_rule = [Rule].Id INNER JOIN
                      Category ON RuleSet_x_Category.id_category = Category.Id
 ) t 
 PIVOT(
    IIF(Measurement.diameter BETWEEN [Rule].Diameter_min AND [Rule].Diameter_max, ''true'', ''false'')
     FOR [Name] IN ('+ @columns +')
 ) AS pivot_table;';

 -- execute the dynamic SQL
 EXECUTE sp_executesql @sql;

这给了我错误：

 Msg 195, Level 15, State 1, Line 12
 'IIF' is not a recognized aggregate function.

这是一个创建演示数据库的脚本，我用它来修补这个问题：

 USE [master]
 GO
 /****** Object:  Database [Demo]    Script Date: 6/10/2020 2:14:59 PM ******/
 CREATE DATABASE [Demo]
  CONTAINMENT = NONE
  ON  PRIMARY 
 ( NAME = N'Demo', FILENAME = N'C:\Program Files\Demo.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
  LOG ON 
 ( NAME = N'Demo_log', FILENAME = N'C:\Program Files\Demo.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 GO
 ALTER DATABASE [Demo] SET COMPATIBILITY_LEVEL = 140
 GO
 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
 begin
 EXEC [Demo].[dbo].[sp_fulltext_database] @action = 'enable'
 end
 GO
 ALTER DATABASE [Demo]SET ANSI_NULL_DEFAULT OFF 
 GO
 ALTER DATABASE [Demo]SET ANSI_NULLS OFF 
 GO
 ALTER DATABASE [Demo]SET ANSI_PADDING OFF 
 GO
 ALTER DATABASE [Demo]SET ANSI_WARNINGS OFF 
 GO
 ALTER DATABASE [Demo]SET ARITHABORT OFF 
 GO
 ALTER DATABASE [Demo]SET AUTO_CLOSE OFF 
 GO
 ALTER DATABASE [Demo]SET AUTO_SHRINK OFF 
 GO
 ALTER DATABASE [Demo]SET AUTO_UPDATE_STATISTICS ON 
 GO
 ALTER DATABASE [Demo]SET CURSOR_CLOSE_ON_COMMIT OFF 
 GO
 ALTER DATABASE [Demo]SET CURSOR_DEFAULT  GLOBAL 
 GO
 ALTER DATABASE [Demo]SET CONCAT_NULL_YIELDS_NULL OFF 
 GO
 ALTER DATABASE [Demo]SET NUMERIC_ROUNDABORT OFF 
 GO
 ALTER DATABASE [Demo]SET QUOTED_IDENTIFIER OFF 
 GO
 ALTER DATABASE [Demo]SET RECURSIVE_TRIGGERS OFF 
 GO
 ALTER DATABASE [Demo]SET  DISABLE_BROKER 
 GO
 ALTER DATABASE [Demo]SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
 GO
 ALTER DATABASE [Demo]SET DATE_CORRELATION_OPTIMIZATION OFF 
 GO
 ALTER DATABASE [Demo]SET TRUSTWORTHY OFF 
 GO
 ALTER DATABASE [Demo]SET ALLOW_SNAPSHOT_ISOLATION OFF 
 GO
 ALTER DATABASE [Demo]SET PARAMETERIZATION SIMPLE 
 GO
 ALTER DATABASE [Demo]SET READ_COMMITTED_SNAPSHOT OFF 
 GO
 ALTER DATABASE [Demo]SET HONOR_BROKER_PRIORITY OFF 
 GO
 ALTER DATABASE [Demo]SET RECOVERY FULL 
 GO
 ALTER DATABASE [Demo]SET  MULTI_USER 
 GO
 ALTER DATABASE [Demo]SET PAGE_VERIFY CHECKSUM  
 GO
 ALTER DATABASE [Demo]SET DB_CHAINING OFF 
 GO
 ALTER DATABASE [Demo]SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
 GO
 ALTER DATABASE [Demo]SET TARGET_RECOVERY_TIME = 60 SECONDS 
 GO
 ALTER DATABASE [Demo]SET DELAYED_DURABILITY = DISABLED 
 GO
 EXEC sys.sp_db_vardecimal_storage_format N'DemoPivot', N'ON'
 GO
 ALTER DATABASE [Demo]SET QUERY_STORE = OFF
 GO
 USE [DemoPivot]
 GO
 /****** Object:  Table [dbo].[Category]    Script Date: 6/10/2020 2:14:59 PM ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[Category](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](70) NOT NULL,
  CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
 (
    [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[Measurement]    Script Date: 6/10/2020 2:14:59 PM ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[Measurement](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Id_Part] [int] NOT NULL,
    [Id_RuleSet] [int] NOT NULL,
    [Diameter] [float] NOT NULL,
  CONSTRAINT [PK_Measurement] PRIMARY KEY CLUSTERED 
 (
    [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[Part]    Script Date: 6/10/2020 2:14:59 PM ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[Part](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](70) NOT NULL,
  CONSTRAINT [PK_Part] PRIMARY KEY CLUSTERED 
 (
    [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[Rule]    Script Date: 6/10/2020 2:14:59 PM ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[Rule](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Diameter_min] [float] NOT NULL,
    [Diameter_max] [float] NOT NULL,
  CONSTRAINT [PK_Rule] PRIMARY KEY CLUSTERED 
 (
    [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[RuleSet]    Script Date: 6/10/2020 2:14:59 PM ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[RuleSet](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
  CONSTRAINT [PK_RuleSet] PRIMARY KEY CLUSTERED 
 (
    [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[RuleSet_x_Category]    Script Date: 6/10/2020 2:14:59 PM ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[RuleSet_x_Category](
    [Id_RuleSet] [int] NOT NULL,
    [Id_Category] [int] NOT NULL,
    [Id_Rule] [int] NOT NULL,
  CONSTRAINT [PK_RuleSet_x_Category] PRIMARY KEY CLUSTERED 
 (
    [Id_RuleSet] ASC,
    [Id_Category] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 SET IDENTITY_INSERT [dbo].[Category] ON 
 GO
 INSERT [dbo].[Category] ([Id], [Name]) VALUES (1, N'A')
 GO
 INSERT [dbo].[Category] ([Id], [Name]) VALUES (2, N'B')
 GO
 INSERT [dbo].[Category] ([Id], [Name]) VALUES (3, N'C')
 GO
 INSERT [dbo].[Category] ([Id], [Name]) VALUES (4, N'D')
 GO
 INSERT [dbo].[Category] ([Id], [Name]) VALUES (5, N'E')
 GO
 INSERT [dbo].[Category] ([Id], [Name]) VALUES (6, N'F')
 GO
 INSERT [dbo].[Category] ([Id], [Name]) VALUES (7, N'G')
 GO
 SET IDENTITY_INSERT [dbo].[Category] OFF
 GO
 SET IDENTITY_INSERT [dbo].[Measurement] ON 
 GO
 INSERT [dbo].[Measurement] ([Id], [Id_Part], [Id_RuleSet], [Diameter]) VALUES (1, 1, 1, 12)
 GO
 SET IDENTITY_INSERT [dbo].[Measurement] OFF
 GO
 SET IDENTITY_INSERT [dbo].[Part] ON 
 GO
 INSERT [dbo].[Part] ([Id], [Name]) VALUES (1, N'myPart')
 GO
 SET IDENTITY_INSERT [dbo].[Part] OFF
 GO
 SET IDENTITY_INSERT [dbo].[Rule] ON 
 GO
 INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (1, 10, 12)
 GO
 INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (2, 11, 13)
 GO
 INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (3, 12, 15)
 GO
 INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (4, 13, 18)
 GO
 INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (5, 13, 19)
 GO
 INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (6, 14, 16)
 GO
 INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (7, 15, 17)
 GO
 SET IDENTITY_INSERT [dbo].[Rule] OFF
 GO
 SET IDENTITY_INSERT [dbo].[RuleSet] ON 
 GO
 INSERT [dbo].[RuleSet] ([Id], [Name]) VALUES (1, N'myRule')
 GO
 SET IDENTITY_INSERT [dbo].[RuleSet] OFF
 GO
 INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 1, 1)
 GO
 INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 2, 2)
 GO
 INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 3, 3)
 GO
 INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 4, 4)
 GO
 INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 5, 5)
 GO
 INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 6, 6)
 GO
 INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 7, 7)
 GO
 ALTER TABLE [dbo].[Measurement]  WITH CHECK ADD  CONSTRAINT [FK_Measurement_Part] FOREIGN KEY([Id_Part])
 REFERENCES [dbo].[Part] ([Id])
 GO
 ALTER TABLE [dbo].[Measurement] CHECK CONSTRAINT [FK_Measurement_Part]
 GO
 ALTER TABLE [dbo].[Measurement]  WITH CHECK ADD  CONSTRAINT [FK_Measurement_RuleSet] FOREIGN KEY([Id_RuleSet])
 REFERENCES [dbo].[RuleSet] ([Id])
 GO
 ALTER TABLE [dbo].[Measurement] CHECK CONSTRAINT [FK_Measurement_RuleSet]
 GO
 ALTER TABLE [dbo].[RuleSet_x_Category]  WITH CHECK ADD  CONSTRAINT [FK_RuleSet_x_Category_Category] FOREIGN KEY([Id_Category])
 REFERENCES [dbo].[Category] ([Id])
 GO
 ALTER TABLE [dbo].[RuleSet_x_Category] CHECK CONSTRAINT [FK_RuleSet_x_Category_Category]
 GO
 ALTER TABLE [dbo].[RuleSet_x_Category]  WITH CHECK ADD  CONSTRAINT [FK_RuleSet_x_Category_Rule] FOREIGN KEY([Id_Rule])
 REFERENCES [dbo].[Rule] ([Id])
 GO
 ALTER TABLE [dbo].[RuleSet_x_Category] CHECK CONSTRAINT [FK_RuleSet_x_Category_Rule]
 GO
 ALTER TABLE [dbo].[RuleSet_x_Category]  WITH CHECK ADD  CONSTRAINT [FK_RuleSet_x_Category_RuleSet] FOREIGN KEY([Id_RuleSet])
 REFERENCES [dbo].[RuleSet] ([Id])
 GO
 ALTER TABLE [dbo].[RuleSet_x_Category] CHECK CONSTRAINT [FK_RuleSet_x_Category_RuleSet]
 GO
 USE [master]
 GO
 ALTER DATABASE [Demo]SET  READ_WRITE 
 GO

（背景：我想将其用作 c# 中实体框架核心的存储过程，然后是具有可变列数的 wpf（mvvm）数据网格的项目源）。

标签： sql-serversql-server-2017

解决方案

我无法显示值“yes”/“no”或“true”/“false”，因为我无法解决 PIVOT 语句要求的聚合函数。

所以我使用了输出 1 或 0 的 CASE 语句，正如 Joel Coehoorn 推荐的那样。

这是我的查询：

DECLARE 
        @columns NVARCHAR(MAX) = '', 
        @sql     NVARCHAR(MAX) = '';

    -- select the category names
    SELECT 
        @columns+=QUOTENAME([Name]) + ','
    FROM 
        Category
    ORDER BY 
        [Name];

    -- remove the last comma
    SET @columns = LEFT(@columns, LEN(@columns) - 1);

    -- construct dynamic SQL
    SET @sql ='
    SELECT * FROM   
    (
     SELECT  Measurement.id_part, Category.Name,
     Case 
        When Measurement.Diameter BETWEEN [RULE].Diameter_min AND [Rule].Diameter_max
            THEN 1
        Else
            0
            End As Hit
     FROM Measurement INNER JOIN
                          RuleSet ON Measurement.id_ruleset = RuleSet.ID INNER JOIN
                          RuleSet_x_Category ON RuleSet.ID = RuleSet_x_Category.Id_RuleSet INNER JOIN
                          [Rule] ON RuleSet_x_Category.id_rule = [Rule].Id INNER JOIN
                          Category ON RuleSet_x_Category.id_category = Category.Id
    ) t 
    PIVOT(
        sum(hit)
        FOR [name] IN ('+ @columns +')
    ) AS pivot_table;';

    -- execute the dynamic SQL
    EXECUTE sp_executesql @sql;

这是我的结果：

在此处输入图像描述

我现在有三个部分，每个部分都有一个测量。如果直径符合规则标准，我会得到 1，如果不是 0。我可以使用它。

我现在创建了一个可以（希望）从我的应用程序运行的存储过程。

CREATE PROCEDURE GetCategories @idmesurement int 
 AS
    DECLARE 
        @columns NVARCHAR(MAX) = '', 
        @sql     NVARCHAR(MAX) = '';

    -- select the category names
    SELECT 
        @columns+=QUOTENAME([Name]) + ','
    FROM 
        Category
    ORDER BY 
        [Name];

    -- remove the last comma
    SET @columns = LEFT(@columns, LEN(@columns) - 1);

    -- construct dynamic SQL
    SET @sql ='
    SELECT * FROM   
    (
     SELECT  Measurement.id_part, Category.Name,
     Case 
        When Measurement.Diameter BETWEEN [RULE].Diameter_min AND [Rule].Diameter_max
            THEN 1
        Else
            0
            End As Hit
     FROM Measurement INNER JOIN
                          RuleSet ON Measurement.id_ruleset = RuleSet.ID INNER JOIN
                          RuleSet_x_Category ON RuleSet.ID = RuleSet_x_Category.Id_RuleSet INNER JOIN
                          [Rule] ON RuleSet_x_Category.id_rule = [Rule].Id INNER JOIN
                          Category ON RuleSet_x_Category.id_category = Category.Id
    WHERE Measurement.id = '+ CONVERT(varchar(10),@idmesurement) +'
    ) t 
    PIVOT(
        sum(hit)
        FOR [name] IN ('+ @columns +')
    ) AS pivot_table;';

    -- execute the dynamic SQL
    EXECUTE sp_executesql @sql;

和

GetCategories @idmesurement = 1

仅输出 id=1 的测量结果。

非常感谢！

推荐阅读