gpt4 book ai didi

c# - 来自数据库中不同列的数据计数

转载 作者:太空宇宙 更新时间:2023-11-03 10:58:37 24 4
gpt4 key购买 nike

我有一个名为tblCar 的表,其中有IDCarNumber 和其他32 个字段。32列填充为s1,s2,s3,.......,s32因为它们专门用于备注是否为可用的座位(A),预订的(B),并售出(S)。

我想计算该数据库中的可用座位数、预订座位数和售罄座位数。我应该如何编写查询?

是否有从数据库中的 32 个不同列中的字​​符串 SBA 进行计数?

我想展示的是这样的..... S = 20, B = 10, A = 2;

SqlCommand cmd = new SqlCommand(
"Select count(*)
FROM tblCar
WHERE s1= 'S' or s2= 'S' or s3= 'S'
or s4= 'S' or s5= 'S' or s6= 'S'
or s7= 'S' or s8= 'S' or s9= 'S'
or s10= 'S' or s11= 'S' or s12= 'S'
or s13= 'S' or s14= 'S' or s15= 'S'
or s16= 'S' or s17= 'S' or s18= 'S'
or s19= 'S' or s20= 'S' or s21= 'S'
or s22= 'S' or s23= 'S' or s24= 'S'
or s25= 'S' or s26= 'S' or s27= 'S'
or s28= 'S' or s29= 'S' or s30= 'S'
or s31= 'S' or s32= 'S' ", con
);
count += cmd.ExecuteNonQuery();

这就是我为“已售出”字段工作的方式。但它只显示计数 = -128。我将计数初始化为 0

最佳答案

试试这个 sql 查询:

SELECT SUM(CASE WHEN State = 'S' THEN 1 ELSE 0 END) AS Sold
, SUM(CASE WHEN State = 'B' THEN 1 ELSE 0 END) AS Booked
, SUM(CASE WHEN State = 'A' THEN 1 ELSE 0 END) AS Available
FROM
(SELECT S1, S2, S3, S4, S5, S6
FROM Seats) s
UNPIVOT
(State FOR Seat IN (S1, S2, S3, S4, S5, S6)) AS rows;

在这里SQL Fiddle .

在示例中,我只使用了 6 列,但在您的情况下,您需要设置所有 32 列,但只需一次

C#代码:

using SqlConnection conn = new SqlConnection(yourConnectionString)
{
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT SUM(CASE WHEN State = 'S' THEN 1 ELSE 0 END) AS Sold");
query.AppendLine(", SUM(CASE WHEN State = 'B' THEN 1 ELSE 0 END) AS Booked");
query.AppendLine(", SUM(CASE WHEN State = 'A' THEN 1 ELSE 0 END) AS Available");
query.AppendLine("FROM ");
query.AppendLine("(SELECT S1, S2, S3, S4, S5, S6, S7, S8");
query.AppendLine("(,S9, S10, S11, S12, S13, S14, S15, S16");
query.AppendLine("(,S17, S18, S19, S20, S21, S22, S23, S24");
query.AppendLine("(,S25, S26, S27, S28, S29, S30, S31, S32");
query.AppendLine("FROM Seats) s");
query.AppendLine("UNPIVOT");
query.AppendLine("(State FOR Seat IN (S1, S2, S3, S4, S5, S6, S7, S8");
query.AppendLine("(,S9, S10, S11, S12, S13, S14, S15, S16");
query.AppendLine("(,S17, S18, S19, S20, S21, S22, S23, S24");
query.AppendLine("(,S25, S26, S27, S28, S29, S30, S31, S32)) AS rows;");

conn.Open();
using SqlCommand command = new SqlCommand(query.ToString(), conn)
{
DataTable data = new DataTable();
data.Load(command.ExecuteReader());
//then get your values
Int32 avialable = 0;
Int32 booked= 0;
Int32 sold = 0;
if(data.Rows.Count > 0)
{
available = (Int32)data(0)("Available");
booked = (Int32)data(0)("Booked");
sold = (Int32)data(0)("Sold");
}
}
}

关于c# - 来自数据库中不同列的数据计数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18536398/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com