居家办公资产管理系统：资产当前库存SQL统计查询需求

Hey there! Let's work through this inventory counting problem for your remote work asset management system. First, I need to make some reasonable assumptions about your database schema (since you didn't share it) — these are typical structures for tracking assets and their movements, so adjust them to fit your actual tables if needed.

Assumed Database Schema #

We'll use two core tables to track asset totals and their lifecycle:

1. assets (Asset Catalog) #

Stores the master list of asset types and their total quantities in the system:

CREATE TABLE assets (
    asset_id INT PRIMARY KEY AUTO_INCREMENT,
    asset_type VARCHAR(50) NOT NULL, -- e.g., 'Mouse'
    total_quantity INT NOT NULL DEFAULT 0 -- Total units of this asset
);

2. asset_transactions (Movement Log) #

Logs every issue, return, or status change for assets. This lets us track who has what, and which assets are damaged:

CREATE TABLE asset_transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    asset_id INT NOT NULL,
    user_name VARCHAR(100) NOT NULL, -- e.g., 'John Doe'
    transaction_type ENUM('ISSUE', 'RETURN') NOT NULL,
    status ENUM('ACTIVE', 'RETURNED', 'DAMAGED') NOT NULL, -- 'ACTIVE' = currently with user
    transaction_date DATE NOT NULL,
    FOREIGN KEY (asset_id) REFERENCES assets(asset_id)
);

Sample Data Matching Your Scenario #

Let's populate these tables with your exact example:

-- Add the Mouse asset (3 total units)
INSERT INTO assets (asset_type, total_quantity) VALUES ('Mouse', 3);

-- Issue 1 Mouse to John Doe (active, in use)
INSERT INTO asset_transactions (asset_id, user_name, transaction_type, status, transaction_date)
VALUES (1, 'John Doe', 'ISSUE', 'ACTIVE', '2024-01-15');

-- Issue 1 Mouse to Jane Doe, then mark it as damaged and returned
INSERT INTO asset_transactions (asset_id, user_name, transaction_type, status, transaction_date)
VALUES (1, 'Jane Doe', 'ISSUE', 'ACTIVE', '2024-01-16');
INSERT INTO asset_transactions (asset_id, user_name, transaction_type, status, transaction_date)
VALUES (1, 'Jane Doe', 'RETURN', 'DAMAGED', '2024-01-20');

SQL Query to Calculate Current Available Stock #

Your goal is to find how many Mice are available to issue (1, in your scenario). The logic here is:
Available Stock = Total Assets - Assets Currently Issued - Damaged Assets

Here's the query that implements this:

SELECT
    a.asset_type,
    a.total_quantity AS total_assets,
    COALESCE(SUM(CASE WHEN t.transaction_type = 'ISSUE' AND t.status = 'ACTIVE' THEN 1 ELSE 0 END), 0) AS active_issued,
    COALESCE(SUM(CASE WHEN t.status = 'DAMAGED' THEN 1 ELSE 0 END), 0) AS damaged_assets,
    (a.total_quantity 
     - COALESCE(SUM(CASE WHEN t.transaction_type = 'ISSUE' AND t.status = 'ACTIVE' THEN 1 ELSE 0 END), 0) 
     - COALESCE(SUM(CASE WHEN t.status = 'DAMAGED' THEN 1 ELSE 0 END), 0)) AS current_available_stock
FROM assets a
LEFT JOIN asset_transactions t ON a.asset_id = t.asset_id
WHERE a.asset_type = 'Mouse' -- Remove this line to get stats for all assets
GROUP BY a.asset_id, a.asset_type, a.total_quantity;

What This Does: #

• COALESCE prevents NULL values if an asset has no transactions yet
• The first CASE counts how many assets are currently in use (issued and active)
• The second CASE counts damaged assets that aren't usable
• Subtracting both from the total gives your available stock

For your scenario, this query will return:

Bonus: Better Approach for Individual Asset Tracking #

If your system tracks each physical asset (with unique serial numbers) instead of just totals per type, you can simplify the query and avoid counting errors. Here's a sample schema and query:

individual_assets Table #

CREATE TABLE individual_assets (
    serial_number VARCHAR(50) PRIMARY KEY,
    asset_type VARCHAR(50) NOT NULL,
    status ENUM('IN_STOCK', 'ISSUED', 'DAMAGED') NOT NULL DEFAULT 'IN_STOCK',
    assigned_user VARCHAR(100) NULL
);

Query for Available Stock #

SELECT
    asset_type,
    COUNT(*) AS total_assets,
    SUM(CASE WHEN status = 'IN_STOCK' THEN 1 ELSE 0 END) AS current_available_stock,
    SUM(CASE WHEN status = 'ISSUED' THEN 1 ELSE 0 END) AS active_issued,
    SUM(CASE WHEN status = 'DAMAGED' THEN 1 ELSE 0 END) AS damaged_assets
FROM individual_assets
WHERE asset_type = 'Mouse'
GROUP BY asset_type;

This is more robust because it tracks each asset's status directly, so you never have to worry about miscounting transactions.

内容的提问来源于stack exchange，提问作者Dametrie