Truy vấn T-SQL nâng cao

-- INNER — chỉ row match cả 2 bảng
SELECT c.Name, o.OrderDate
FROM Customers c
INNER JOIN Orders o ON o.CustomerId = c.Id;

-- LEFT — tất cả customer + đơn nếu có
SELECT c.Name, o.OrderDate
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id;

-- RIGHT — đảo lại (ít dùng vì khó đọc, swap LEFT thuận hơn)
SELECT c.Name, o.OrderDate
FROM Orders o
RIGHT JOIN Customers c ON o.CustomerId = c.Id;

-- FULL OUTER — tất cả row 2 bên
SELECT c.Name, o.OrderDate
FROM Customers c
FULL OUTER JOIN Orders o ON o.CustomerId = c.Id;

-- CROSS — tích Descartes (m × n)
SELECT c.Name, p.Name
FROM Customers c CROSS JOIN Products p;

-- Bảng Employees có ManagerId trỏ về Employees.Id
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON m.Id = e.ManagerId;

-- Khách hàng chưa từng đặt đơn — 3 cách
-- 1. LEFT JOIN ... IS NULL
SELECT c.* FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id
WHERE o.Id IS NULL;

-- 2. NOT EXISTS (thường tốt nhất với SQL Server)
SELECT c.* FROM Customers c
WHERE NOT EXISTS (
    SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id
);

-- 3. NOT IN (cẩn thận NULL!)
SELECT c.* FROM Customers c
WHERE c.Id NOT IN (
    SELECT CustomerId FROM Orders WHERE CustomerId IS NOT NULL
);

SELECT Name,
    (SELECT COUNT(*) FROM Orders WHERE CustomerId = c.Id) AS OrderCount
FROM Customers c;

-- Top 3 sản phẩm đắt nhất MỖI category
SELECT * FROM Products p1
WHERE (
    SELECT COUNT(*) FROM Products p2
    WHERE p2.CategoryId = p1.CategoryId AND p2.Price > p1.Price
) < 3;

-- Khách có ít nhất 1 đơn > 10 triệu
SELECT * FROM Customers c
WHERE EXISTS (
    SELECT 1 FROM Orders o
    WHERE o.CustomerId = c.Id AND o.Total > 10000000
);

SELECT t.City, t.TotalRevenue
FROM (
    SELECT c.City, SUM(o.Total) AS TotalRevenue
    FROM Customers c JOIN Orders o ON o.CustomerId = c.Id
    GROUP BY c.City
) t
WHERE t.TotalRevenue > 100000000;

-- UNION — gộp + loại trùng (chậm hơn)
SELECT Email FROM Customers
UNION
SELECT Email FROM Suppliers;

-- UNION ALL — gộp, không loại trùng (NHANH HƠN)
SELECT Email FROM Customers
UNION ALL
SELECT Email FROM Suppliers;

-- INTERSECT — giao
SELECT Email FROM Customers
INTERSECT
SELECT Email FROM Suppliers;

-- EXCEPT — hiệu (A trừ B)
SELECT Email FROM Customers
EXCEPT
SELECT Email FROM Suppliers;

WITH HighValueOrders AS (
    SELECT * FROM Orders WHERE Total > 10000000
)
SELECT c.Name, COUNT(*) AS BigOrderCount
FROM HighValueOrders o
JOIN Customers c ON c.Id = o.CustomerId
GROUP BY c.Name;

WITH 
TopCustomers AS (
    SELECT TOP 10 CustomerId, SUM(Total) AS Spent
    FROM Orders GROUP BY CustomerId
    ORDER BY Spent DESC
),
TheirOrders AS (
    SELECT o.* FROM Orders o
    WHERE o.CustomerId IN (SELECT CustomerId FROM TopCustomers)
)
SELECT * FROM TheirOrders WHERE OrderDate >= '2026-01-01';

-- Build cây tổ chức từ root xuống
WITH OrgTree AS (
    -- Anchor: root (không có manager)
    SELECT Id, Name, ManagerId, 0 AS Lvl, CAST(Name AS NVARCHAR(MAX)) AS Path
    FROM Employees WHERE ManagerId IS NULL
    
    UNION ALL
    
    -- Recursive: tìm nhân viên có manager đã ở trong tree
    SELECT e.Id, e.Name, e.ManagerId, t.Lvl + 1,
           CAST(t.Path + N' > ' + e.Name AS NVARCHAR(MAX))
    FROM Employees e
    JOIN OrgTree t ON t.Id = e.ManagerId
)
SELECT * FROM OrgTree ORDER BY Path;

function_name() OVER (
    [PARTITION BY col1, col2 ...]
    [ORDER BY col1 [ASC|DESC] ...]
    [ROWS|RANGE BETWEEN ... AND ...]
)

-- Cùng dữ liệu: 100, 100, 90, 80
SELECT
    Value,
    ROW_NUMBER() OVER (ORDER BY Value DESC) AS Rn,    -- 1, 2, 3, 4
    RANK()       OVER (ORDER BY Value DESC) AS Rk,    -- 1, 1, 3, 4 (skip)
    DENSE_RANK() OVER (ORDER BY Value DESC) AS Dk     -- 1, 1, 2, 3 (no skip)
FROM Scores;

-- Đơn hàng MỚI NHẤT của MỖI khách
WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY CustomerId
        ORDER BY OrderDate DESC
    ) AS Rn
    FROM Orders
)
SELECT * FROM Ranked WHERE Rn = 1;

SELECT
    OrderDate,
    Total,
    SUM(Total) OVER (ORDER BY OrderDate) AS RunningTotal,
    AVG(Total) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS MovingAvg7Days
FROM Orders;

SELECT
    OrderDate, Total,
    LAG(Total) OVER (ORDER BY OrderDate) AS PrevTotal,
    Total - LAG(Total) OVER (ORDER BY OrderDate) AS DeltaFromPrev
FROM Orders;

-- Chia khách thành 4 quartile theo total spend
SELECT CustomerId, Spent,
    NTILE(4) OVER (ORDER BY Spent DESC) AS Quartile
FROM (
    SELECT CustomerId, SUM(Total) AS Spent
    FROM Orders GROUP BY CustomerId
) t;

-- Tổng theo nhiều combination cùng lúc — báo cáo
SELECT City, Status, COUNT(*) AS Cnt
FROM Orders o JOIN Customers c ON c.Id = o.CustomerId
GROUP BY GROUPING SETS (
    (City, Status),       -- detail
    (City),               -- subtotal theo City
    (Status),             -- subtotal theo Status
    ()                    -- grand total
);

-- ROLLUP = subtotal lũy tiến
GROUP BY ROLLUP (Year, Month, Day);

-- CUBE = mọi combination
GROUP BY CUBE (City, Status);

-- WHERE lọc TRƯỚC group, HAVING lọc SAU group
SELECT City, COUNT(*) cnt
FROM Customers
WHERE IsActive = 1                 -- trước group
GROUP BY City
HAVING COUNT(*) > 5;               -- sau group

-- Gộp tên người trong cùng city thành 1 string
SELECT City, STRING_AGG(Name, ', ')
    WITHIN GROUP (ORDER BY Name) AS People
FROM Customers
GROUP BY City;

-- Doanh thu theo tháng, hàng = sản phẩm
SELECT *
FROM (
    SELECT ProductName, MONTH(OrderDate) AS m, Total
    FROM Orders
) src
PIVOT (
    SUM(Total) FOR m IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS p;

BEGIN TRANSACTION;
BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 1000 WHERE Id = 1;
    UPDATE Accounts SET Balance = Balance + 1000 WHERE Id = 2;
    
    IF (SELECT Balance FROM Accounts WHERE Id = 1) < 0
        THROW 50001, N'Insufficient funds', 1;
    
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;            -- rethrow để app biết
END CATCH

BEGIN TRANSACTION;
INSERT INTO Logs VALUES ('Step 1');
SAVE TRANSACTION sp1;

INSERT INTO Logs VALUES ('Step 2');
IF (@@ERROR <> 0) ROLLBACK TRANSACTION sp1;   -- rollback về step 1 thôi

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- ...transaction body...

-- Hoặc per query (hint)
SELECT * FROM Orders WITH (NOLOCK);    -- = READ UNCOMMITTED — dirty read OK

-- Bật theo dõi deadlock trong session
DBCC TRACEON (1222, -1);   -- log deadlock graph ra ERRORLOG

-- Bat CE Feedback (mac dinh ON trong 2025)
ALTER DATABASE SCOPED CONFIGURATION
SET CE_FEEDBACK = ON;

-- Kiem tra feedback da duoc ap dung
SELECT * FROM sys.query_store_plan_feedback
WHERE feature_id = 1;  -- CE feedback

-- Bat PSPO (mac dinh ON trong 2025)
ALTER DATABASE SCOPED CONFIGURATION
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

-- Pattern pho bien gay van de:
SELECT * FROM Orders
WHERE (@CustomerId IS NULL OR CustomerId = @CustomerId)
  AND (@Status IS NULL OR Status = @Status);
-- ⚠️ OPTION (RECOMPILE) truoc day la cach chua duy nhat

-- Bat Optimized Locking (mac dinh ON trong 2025)
ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_LOCKING = ON;

-- Pattern duoc LAQ toi uu:
UPDATE Orders SET Status = 'Processed'
WHERE Status = 'Pending' AND OrderDate < '2026-01-01';

-- Query nay gio chay ~70% nhanh hon nho batch mode
SELECT
    ABS(PriceChange) AS AbsoluteChange,
    SIN(RADIANS(Latitude)) AS SinLat,
    DATE_TRUNC('month', OrderDate) AS OrderMonth
FROM Orders
WHERE OrderDate >= '2026-01-01';

-- Thay vi sp_executesql thong thuong
EXEC sp_executesql @sql, @params, @p1, @p2;

-- Dung OPTIMIZED variant
EXEC sys.OPTIMIZED_SP_EXECUTESQL @sql, @params, @p1, @p2;

-- Tim 10 san pham tuong tu nhat, kem loc theo category + gia
DECLARE @query_vector VECTOR(1536);
-- @query_vector duoc tao tu embedding model (OpenAI, v.v.)

SELECT TOP 10
    p.name,
    p.price,
    VECTOR_DISTANCE('cosine', p.embedding, @query_vector) AS score
FROM products p
WHERE p.category_id = 5
    AND p.price BETWEEN 100 AND 1000
ORDER BY score ASC;

-- Dung VECTOR_SEARCH de lay tap ung vien, sau do JOIN loc them
WITH semantic AS (
    SELECT
        id,
        VECTOR_DISTANCE('cosine', embedding, @query_vector) AS score
    FROM products
    WHERE VECTOR_SEARCH(embedding, @query_vector, 100)
)
SELECT p.*, s.score
FROM products p
JOIN semantic s ON p.id = s.id
WHERE p.in_stock = 1
ORDER BY s.score ASC;

-- Tao vector index cho tim kiem ANN (Approximate Nearest Neighbor)
CREATE VECTOR INDEX idx_product_embedding
ON products (embedding)
WITH (DISTANCE_METRIC = 'cosine', INDEX_TYPE = 'IVFFLAT');

-- Query su dung index
SELECT TOP 20 name, VECTOR_DISTANCE('cosine', embedding, @query_vector) AS score
FROM products
ORDER BY score ASC;

-- JSON_TABLE: tuong tu MySQL, chuyen JSON array thanh cac row
SELECT j.*
FROM orders
CROSS APPLY OPENJSON(order_data, '$.items')
    WITH (
        product_id INT       '$.product_id',
        qty        INT       '$.qty',
        price      DECIMAL(10,2) '$.price'
    ) AS j;

-- Kiem tra JSON co chua gia tri 'error' trong field $.level khong
SELECT *
FROM logs
WHERE JSON_CONTAINS(payload, 'error', '$.level');

-- Tim san pham co tag 'sale'
SELECT *
FROM products
WHERE JSON_CONTAINS(tags, '"sale"');

SELECT *
FROM documents
WHERE JSON_PATH_EXISTS(metadata, '$.author.email');

-- Tim tai lieu chua tu khoa nhay cam (case-insensitive)
SELECT *
FROM documents
WHERE REGEXP_LIKE(content, '\b(confidential|secret|internal)\b', 'i');

-- Validate email format
SELECT *
FROM users
WHERE NOT REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Trich xuat dia chi IP tu log
SELECT
    id,
    REGEXP_SUBSTR(description, '\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}') AS ip_address
FROM logs;

-- Trich xuat so dien thoai
SELECT REGEXP_SUBSTR(notes, '\d{2,4}[-\s]?\d{3,4}[-\s]?\d{4}') AS phone
FROM contacts;

-- Mask so dien thoai trong log
SELECT REGEXP_REPLACE(
    log_message,
    '\d{2,4}[-\s]?\d{3,4}[-\s]?\d{4}',
    '***MASKED***'
) AS sanitized_message
FROM audit_logs;

-- Dem so URL trong bai viet
SELECT
    id,
    REGEXP_COUNT(content, 'https?://[^\s]+') AS url_count
FROM articles;

-- Kiem tra IQP features dang bat
SELECT name, value_in_use
FROM sys.configurations
WHERE name LIKE '%intelligent%'
   OR name LIKE '%feedback%'
   OR name LIKE '%adaptive%';

-- Bat tat ca IQP features (2025, mac dinh ON)
ALTER DATABASE SCOPED CONFIGURATION
SET CE_FEEDBACK = ON,
    DOP_FEEDBACK = ON,
    MEMORY_GRANT_FEEDBACK = ON,
    OPTIMIZED_LOCKING = ON,
    PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;