T-SQL语句使用

佩灵仙

已于 2024-05-24 10:07:18 修改

阅读量864

点赞数 25

文章标签： sql 数据库

于 2024-05-24 09:54:21 首次发布

本文链接：https://blog.csdn.net/weixin_47560724/article/details/139162644

版权

1. sql 统计一张表有多少列

在 SQL 中，要统计一张表有多少列，可以使用不同数据库系统提供的系统目录表或信息模式。以下是一些常见数据库系统的查询示例：

MySQL

在 MySQL 中，你可以使用 INFORMATION_SCHEMA.COLUMNS 表来查询表中的列数：

SELECT COUNT(*) AS column_count
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

将 your_database_name 和 your_table_name 替换为实际的数据库和表名。

PostgreSQL

在 PostgreSQL 中，你可以使用 information_schema.columns 视图来查询：

SELECT COUNT(*)
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'your_table_name';

将 your_table_name 替换为实际的表名。public 是 PostgreSQL 中默认的 schema 名称。

SQL Server

在 SQL Server 中，你可以使用 INFORMATION_SCHEMA.COLUMNS 视图：

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'dbo';

将 your_table_name 替换为实际的表名。dbo 是 SQL Server 中默认的 schema 名称。

Oracle

在 Oracle 中，你可以使用 ALL_TAB_COLUMNS 视图来查询：

SELECT COUNT(*)
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME' AND OWNER = 'YOUR_SCHEMA';

将 YOUR_TABLE_NAME 和 YOUR_SCHEMA 替换为实际的表名和 schema 名称。
请注意，查询系统目录表或信息模式可能需要相应的权限。如果你没有足够的权限，可能需要联系数据库管理员来获取这些信息。

2. sql一张表内每组取5条数据

在 SQL 中，如果你想从一张表中每组取前 5 条数据，可以使用窗口函数（如 ROW_NUMBER()）来为每组数据分配一个唯一的序号，然后根据这个序号来筛选出每组的前 5 条记录。以下是一个通用的示例，展示了如何使用窗口函数来实现这个需求：

WITH RankedData AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY GroupColumn ORDER BY SomeColumn) AS RowNum
    FROM
        YourTable
)
SELECT *
FROM RankedData
WHERE RowNum <= 5;

在这个示例中，YourTable 是你要查询的表名，GroupColumn 是用于分组的列，SomeColumn 是在每组内进行排序的列。ROW_NUMBER() 函数会为每组数据分配一个唯一的序号，PARTITION BY 子句指定了分组的依据，ORDER BY 子句指定了每组内的排序顺序。
请根据你的具体需求替换 YourTable、GroupColumn 和 SomeColumn。例如，如果你有一个销售记录表 Sales，你想按 Region 分组并按照 SaleDate 排序，然后取每组的前 5 条记录，可以这样写：

WITH RankedSales AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY Region ORDER BY SaleDate) AS RowNum
    FROM
        Sales
)
SELECT *
FROM RankedSales
WHERE RowNum <= 5;

这将返回每个区域按销售日期排序的前 5 条销售记录。

3. sql 一张表内横向列变纵向列

在 SQL 中，将表中的横向列转换为纵向列通常称为“列转行”或“UNPIVOT”。不同的数据库系统提供了不同的方法来实现这一操作。以下是一些常见数据库系统的示例：

SQL Server

SQL Server 提供了 UNPIVOT 函数来实现列转行：

SELECT
    Name,
    'Value' AS ValueType,
    Value
FROM
    (SELECT Name, Column1, Column2, Column3 FROM YourTable) AS SourceTable
UNPIVOT
(
    Value FOR ValueType IN (Column1, Column2, Column3)
) AS Unpivoted;

在这个例子中，YourTable 是包含多列的表，我们想要将这些列转换为行。UNPIVOT 操作将 Column1、Column2 和 Column3 这三列转换成两列：ValueType 列包含原始列的名称，Value 列包含原始列的值。

Oracle

Oracle 也提供了 UNPIVOT 操作：

SELECT
    Name,
    ValueType,
    Value
FROM
    YourTable
UNPIVOT
(
    Value FOR ValueType IN (Column1 AS 'Type1', Column2 AS 'Type2', Column3 AS 'Type3')
);

在这个例子中，YourTable 同样是包含多列的表，我们使用 UNPIVOT 来转换这些列。ValueType 列将包含 ‘Type1’、‘Type2’ 和 ‘Type3’ 作为列名的别名，而 Value 列将包含这些列的值。

PostgreSQL

PostgreSQL 没有直接的 UNPIVOT 函数，但可以使用 UNION ALL 来实现类似的效果：

SELECT
    Name,
    'Column1' AS ValueType,
    Column1 AS Value
FROM
    YourTable
UNION ALL
SELECT
    Name,
    'Column2' AS ValueType,
    Column2 AS Value
FROM
    YourTable
UNION ALL
SELECT
    Name,
    'Column3' AS ValueType,
    Column3 AS Value
FROM
    YourTable;

在这个例子中，我们为 YourTable 中的每一列创建了一个子查询，然后将这些子查询通过 UNION ALL 操作合并起来。每个子查询都选择了 Name 列和其中一个列的值，同时为这个值指定了一个别名作为 ValueType。

MySQL

MySQL 也没有内置的 UNPIVOT 功能，但可以使用 UNION ALL 与 CASE 语句来实现：

SELECT
    Name,
    'Column1' AS ValueType,
    Column1 AS Value
FROM
    YourTable
UNION ALL
SELECT
    Name,
    'Column2' AS ValueType,
    Column2 AS Value
FROM
    YourTable
UNION ALL
SELECT
    Name,
    'Column3' AS ValueType,
    Column3 AS Value
FROM
    YourTable;

这与 PostgreSQL 的示例类似，我们使用 UNION ALL 来合并多个子查询，每个子查询都选择了 Name 列和其中一个列的值。
请根据你的具体需求替换 YourTable、Name 以及 Column1、Column2、Column3 等列名。如果你的表中有大量的列，你可能需要动态生成 SQL 语句来实现列转行。

4. UNPIVOT函数的用法

UNPIVOT 是一个 SQL 函数，用于将表中的列转换为行。这通常在处理报表或需要将宽格式数据转换为长格式数据时使用。UNPIVOT 函数不是 SQL 标准的一部分，因此在不同的数据库系统中的可用性和语法可能会有所不同。以下是两个常见数据库系统中 UNPIVOT 函数的用法示例：

SQL Server

在 SQL Server 中，UNPIVOT 函数的语法如下：

SELECT <unpivoted_column_names>
FROM <table_name> AS <alias_name>
UNPIVOT
(
    <unpivoted_value_column> FOR <unpivoted_type_column> IN (<column_name1>, <column_name2>, ...)
) AS <unpivot_alias>

这里，<unpivoted_column_names> 是你希望在结果集中包含的列名，<table_name> 是你要从中提取数据的表名，<alias_name> 是表的别名，<unpivoted_value_column> 是新行中包含值的列的名称，<unpivoted_type_column> 是新行中包含原始列名的列的名称，而 <column_name1>, <column_name2>, ... 是你想要展开的列名。
例如，假设你有以下表 Sales：

Name | Q1 | Q2 | Q3 | Q4
-----+----+----+----+----
ProductA | 100 | 150 | 200 | 250
ProductB | 200 | 250 | 300 | 350

你想将季度销售数据从列转换为行，可以使用以下 SQL 语句：

SELECT Name, Quarter, SalesAmount
FROM Sales
UNPIVOT
(
    SalesAmount FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS Unpvt;

这将返回以下结果：

Name  | Quarter | SalesAmount
------+---------+------------
ProductA | Q1      | 100
ProductA | Q2      | 150
ProductA | Q3      | 200
ProductA | Q4      | 250
ProductB | Q1      | 200
ProductB | Q2      | 250
ProductB | Q3      | 300
ProductB | Q4      | 350

Oracle

在 Oracle 中，UNPIVOT 函数的语法略有不同：

SELECT <column_names>
FROM <table_name>
UNPIVOT (INCLUDE NULLS)
(
    <value_column> FOR <type_column> IN (<column_name1> AS <alias1>, <column_name2> AS <alias2>, ...)
)

这里，<column_names> 是你希望在结果集中包含的列名，<table_name> 是你要从中提取数据的表名，<value_column> 是新行中包含值的列的名称，<type_column> 是新行中包含原始列名的列的名称，而 <column_name1> AS <alias1>, <column_name2> AS <alias2>, ... 是你想要展开的列名及其别名。
例如，使用与 SQL Server 相同的 Sales 表，你可以使用以下 SQL 语句：

SELECT Name, Quarter, SalesAmount
FROM Sales
UNPIVOT (INCLUDE NULLS)
(
    SalesAmount FOR Quarter IN (Q1 AS 'Q1', Q2 AS 'Q2', Q3 AS 'Q3', Q4 AS 'Q4')
);