用于计算平均值并插入表中的 SQL 查询

debugcn 发表于 Dev

沙希卜

我正在处理歌曲档案数据库，但遇到了一些查询。我想 -

通过他们的平均计算每个用户的评价Comments值score，并插入到评级Users
计算每个用户有多少购买
计算平均值score的Song从Comments表
计算credits每个用户在购买上花费了多少

您可以在下面找到我的表...

CREATE TABLE Users
(
    username   NVARCHAR( 30 )   NOT NULL   PRIMARY KEY,
    pass       NVARCHAR( 16 ),
    email      NVARCHAR( 50 ),
    city       NVARCHAR( 10 ),
    credits    INT,
    rating     INT
)

CREATE TABLE Songs
(
    song_id       INT              NOT NULL   IDENTITY ( 1, 1 )   PRIMARY KEY,
    song_name     NVARCHAR( 30 ),
    username      NVARCHAR( 30 ),
    genre         INT,
    price         INT,
    song_length   INT,
    listens       INT
)

CREATE TABLE Genres
(
    genre_id     INT              NOT NULL   IDENTITY ( 1, 1 )   PRIMARY KEY,
    genre_name   NVARCHAR( 16 )
)


CREATE TABLE Purchases
(
    purchase_id      INT              NOT NULL   IDENTITY ( 1, 1 )   PRIMARY KEY,
    song_id          INT,
    username         NVARCHAR( 30 )
    date_purchased   DATETIME
)

CREATE TABLE Comments
(
    comment_id   INT   NOT NULL   IDENTITY ( 1, 1 )   PRIMARY KEY,
    username     NVARCHAR( 30 ),
    song_id      INT,
    text         NVARCHAR( 30 ),
    score        INT
)

健康服务中心

我回答了你的一些问题。除了各自的查询之外，我将它们安排为公共表表达式，我认为这可能是使用它们的一种便捷方式......

计算每个用户在购买上花费了多少积分，可能需要了解您关于用户如何投资积分的逻辑。

    WITH CTE_PurchasesByUser AS
    (
        SELECT p.username as username,  count(*) as NrOfPurchases
        FROM Purchases p
        GROUP BY p.username
    ),
    CTE_AverageScoreBySong AS
    (
        SELECT c.song_id as song_id, (sum(c.score)/count(c.score)) as AverageScore
        FROM Comments c
        GROUP BY c.song_id
    ),
    CTE_AverageScoreByUser AS
    (
        SELECT u.username as username, (sum(c.score)/count(c.score)) as AverageScore
        FROM Users u
        INNER JOIN Comments c ON u.username = c.username
        GROUP BY u.username
    )
    SELECT u.*, ISNULL(bbu.NrOfPurchases,0), asu.AverageScore 
    FROM Users u
    LEFT JOIN CTE_PurchasesByUser bbu ON u.username = bbu.username
    LEFT JOIN CTE_AverageScoreByUser asu ON u.username = asu.username

此 SQL 与您的表一起运行，但我没有使用数据行对其进行测试...

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。