国产av日韩一区二区三区精品,成人性爱视频在线观看,国产,欧美,日韩,一区,www.成色av久久成人,2222eeee成人天堂

Table of Contents
使用 CASE WHEN 手動(dòng)轉(zhuǎn)置
利用 PIVOT(僅限部分?jǐn)?shù)據(jù)庫)
轉(zhuǎn)置回來:列變行怎么辦?
Home Database SQL How to pivot data in SQL?

How to pivot data in SQL?

Jul 02, 2025 am 12:48 AM

SQL中轉(zhuǎn)置數(shù)據(jù)可通過CASE WHEN手動(dòng)實(shí)現(xiàn)行轉(zhuǎn)列,適用于數(shù)據(jù)量小或需靈活控制的情況;使用PIVOT語法(如Oracle、SQL Server)可簡化操作,但MySQL不支持;列變行可用UNION ALL或UNPIVOT實(shí)現(xiàn);動(dòng)態(tài)列需用動(dòng)態(tài)SQL處理。具體方法應(yīng)根據(jù)數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)庫類型選擇。

How to pivot data in SQL?

SQL里轉(zhuǎn)置數(shù)據(jù),其實(shí)就是把行變列或者列變行,這個(gè)操作在做報(bào)表或者數(shù)據(jù)分析時(shí)挺常見的。關(guān)鍵點(diǎn)在于你得知道原始數(shù)據(jù)長什么樣,目標(biāo)格式又是什么樣,然后根據(jù)情況選合適的方法。

How to pivot data in SQL?

使用 CASE WHEN 手動(dòng)轉(zhuǎn)置

如果你的數(shù)據(jù)量不大,或者需要更靈活控制字段映射,可以用 CASE WHEN 語句來手動(dòng)實(shí)現(xiàn)行轉(zhuǎn)列。

How to pivot data in SQL?

舉個(gè)例子,假設(shè)你有一張銷售記錄表,每條記錄是一個(gè)銷售人員在某個(gè)月的銷售額:

SELECT name, month, sales
FROM sales_data;

你想把每個(gè)月的銷售額變成單獨(dú)的列,可以這樣寫:

How to pivot data in SQL?
SELECT name,
       MAX(CASE WHEN month = 'Jan' THEN sales END) AS jan_sales,
       MAX(CASE WHEN month = 'Feb' THEN sales END) AS feb_sales
FROM sales_data
GROUP BY name;

注意這里用了 MAX() 是因?yàn)橐浜?GROUP BY 消除空值,即使你知道每個(gè) name 和 month 是唯一的,也最好加上聚合函數(shù)防止報(bào)錯(cuò)。


利用 PIVOT(僅限部分?jǐn)?shù)據(jù)庫)

某些數(shù)據(jù)庫比如 Oracle 或者 SQL Server 支持 PIVOT 語法,能簡化行轉(zhuǎn)列的操作。

比如在 SQL Server 中,你可以這樣寫:

SELECT * FROM (
    SELECT name, month, sales
    FROM sales_data
) AS src
PIVOT (
    SUM(sales)
    FOR month IN ([Jan], [Feb], [Mar])
) AS pvt;

但要注意的是,并不是所有數(shù)據(jù)庫都支持 PIVOT,MySQL 就不原生支持,只能用前面說的 CASE WHEN 替代。


轉(zhuǎn)置回來:列變行怎么辦?

有時(shí)候你也可能需要反過來,把多個(gè)列合并成多行。這時(shí)候可以用 UNION ALL 或者 UNPIVOT(同樣只在部分?jǐn)?shù)據(jù)庫中可用)。

比如用 UNION ALL 的方式:

SELECT name, 'Jan' AS month, jan_sales AS sales FROM sales_wide
UNION ALL
SELECT name, 'Feb', feb_sales FROM sales_wide;

雖然寫起來有點(diǎn)啰嗦,但兼容性好,適用于大多數(shù)數(shù)據(jù)庫。


遇到動(dòng)態(tài)列的時(shí)候就復(fù)雜點(diǎn)了,比如你不知道有多少個(gè)月份,那就得用動(dòng)態(tài) SQL 來拼接語句了。不過那屬于進(jìn)階內(nèi)容,平時(shí)處理固定列的話,上面這些方法基本夠用。

基本上就這些,看數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)庫類型選合適的方法就行。

The above is the detailed content of How to pivot data in SQL?. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undress AI Tool

Undress AI Tool

Undress images for free

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

PHP Tutorial
1502
276
How to find columns with a specific name in a SQL database? How to find columns with a specific name in a SQL database? Jul 07, 2025 am 02:08 AM

To find columns with specific names in SQL databases, it can be achieved through system information schema or the database comes with its own metadata table. 1. Use INFORMATION_SCHEMA.COLUMNS query is suitable for most SQL databases, such as MySQL, PostgreSQL and SQLServer, and matches through SELECTTABLE_NAME, COLUMN_NAME and combined with WHERECOLUMN_NAMELIKE or =; 2. Specific databases can query system tables or views, such as SQLServer uses sys.columns to combine sys.tables for JOIN query, PostgreSQL can be used through inf

Comparing Different SQL Dialects (e.g., MySQL, PostgreSQL, SQL Server) Comparing Different SQL Dialects (e.g., MySQL, PostgreSQL, SQL Server) Jul 07, 2025 am 02:02 AM

SQLdialectsdifferinsyntaxandfunctionality.1.StringconcatenationusesCONCAT()inMySQL,||orCONCAT()inPostgreSQL,and inSQLServer.2.NULLhandlingemploysIFNULL()inMySQL,ISNULL()inSQLServer,andCOALESCE()commonacrossall.3.Datefunctionsvary:NOW(),DATE_FORMAT()i

What is the difference between SQL and NoSQL What is the difference between SQL and NoSQL Jul 08, 2025 am 01:52 AM

The core difference between SQL and NoSQL databases is data structure, scaling method and consistency model. 1. In terms of data structure, SQL uses predefined patterns to store structured data, while NoSQL supports flexible formats such as documents, key values, column families and graphs to process unstructured data; 2. In terms of scalability, SQL usually relies on stronger hardware on vertical expansion, while NoSQL realizes distributed expansion through horizontal expansion; 3. In terms of consistency, SQL follows ACID to ensure strong consistency and is suitable for financial systems, while NoSQL mostly uses BASE models to emphasize availability and final consistency; 4. In terms of query language, SQL provides standardized and powerful query capabilities, while NoSQL query languages ??are diverse but not as mature and unified as SQL.

Advantages of Using Common Table Expressions (CTEs) in SQL. Advantages of Using Common Table Expressions (CTEs) in SQL. Jul 07, 2025 am 01:46 AM

The main advantages of CTEs in SQL queries include improving readability, supporting recursive queries, avoiding duplicate subqueries, and enhancing modular and debugging capabilities. 1. Improve readability: By splitting complex queries into multiple independent logical blocks, the structure is clearer; 2. Support recursive queries: The logic is simpler when processing hierarchical data, suitable for deep traversal; 3. Avoid duplicate subqueries: define multiple references at a time, reduce redundancy and improve efficiency; 4. Better modularization and debugging capabilities: Each CTE block can be run and verified separately, making it easier to troubleshoot problems.

When to use SQL subqueries versus joins for data retrieval. When to use SQL subqueries versus joins for data retrieval. Jul 14, 2025 am 02:29 AM

Whether to use subqueries or connections depends on the specific scenario. 1. When it is necessary to filter data in advance, subqueries are more effective, such as finding today's order customers; 2. When merging large-scale data sets, the connection efficiency is higher, such as obtaining customers and their recent orders; 3. When writing highly readable logic, the subqueries structure is clearer, such as finding hot-selling products; 4. When performing updates or deleting operations that depend on related data, subqueries are the preferred solution, such as deleting users that have not been logged in for a long time.

What is a composite primary key in SQL? What is a composite primary key in SQL? Jul 08, 2025 am 01:38 AM

AcompositeprimarykeyinSQLisaprimarykeycomposedoftwoormorecolumnsthattogetheruniquelyidentifyeachrow.1.Itisusedwhennosinglecolumncanensurerowuniqueness,suchasinastudent-courseenrollmenttablewherebothStudentIDandCourseIDarerequiredtoformauniquecombinat

How to find the second highest salary in SQL How to find the second highest salary in SQL Jul 14, 2025 am 02:06 AM

There are three core methods to find the second highest salary: 1. Use LIMIT and OFFSET to skip the maximum salary and get the maximum, which is suitable for small systems; 2. Exclude the maximum value through subqueries and then find MAX, which is highly compatible and suitable for complex queries; 3. Use DENSE_RANK or ROW_NUMBER window function to process parallel rankings, which is highly scalable. In addition, it is necessary to combine IFNULL or COALESCE to deal with the absence of a second-highest salary.

How to create empty tables with the same structure as another table? How to create empty tables with the same structure as another table? Jul 11, 2025 am 01:51 AM

You can use SQL's CREATETABLE statement and SELECT clause to create a table with the same structure as another table. The specific steps are as follows: 1. Create an empty table using CREATETABLEnew_tableASSELECT*FROMexisting_tableWHERE1=0;. 2. Manually add indexes, foreign keys, triggers, etc. when necessary to ensure that the new table is intact and consistent with the original table structure.

See all articles