22,300
社区成员




SELECT
a.gid,a.qupy,a.qpyfl,a.dingdu,MAX(CASE WHEN b.spgid IS NOT NULL THEN b.payzt ELSE 0 END) AS payzt
FROM shipin AS a
LEFT JOIN dingdan AS b ON a.gid=b.spgid AND b.hygid=@hygid --外面传进来的值
WHERE a.qupy=@qupy AND a.qpyfl=@qpyfl --外面传进来的值
GROUP BY a.gid,a.qupy,a.qpyfl,a.dingdu
SqlParameter[] parameters = {
new SqlParameter("@TableName", SqlDbType.VarChar,20),
new SqlParameter("@Fields", SqlDbType.VarChar,500),
new SqlParameter("@Where", SqlDbType.VarChar,500),
new SqlParameter("@OrderBy", SqlDbType.VarChar,200),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int)};
parameters[0].Value = TableName;
parameters[1].Value = Fields;
parameters[2].Value = LJSheng.Common.LCommon.ClearSql(Where);
parameters[3].Value = OrderBy;
parameters[4].Value = PageSize;
parameters[5].Value = PageIndex;
//return DbHelperSQL.RunProcedure("Table_List", parameters, "ds");
//不调用存储过程直接执行查询语句
string Row = "";
string TableWhere = parameters[0].SqlValue.ToString();
int StartNumber;
int EndNumber;
if (!String.IsNullOrEmpty(parameters[2].SqlValue.ToString()))
{
TableWhere = parameters[0].SqlValue.ToString() + " where " + parameters[2].SqlValue.ToString();
}
if (parameters[4].SqlValue.ToString() != "0")
{
StartNumber = (Int32.Parse(parameters[5].SqlValue.ToString()) - 1) * Int32.Parse(parameters[4].SqlValue.ToString()) + 1;
EndNumber = (Int32.Parse(parameters[5].SqlValue.ToString()) - 1) * Int32.Parse(parameters[4].SqlValue.ToString()) + Int32.Parse(parameters[4].SqlValue.ToString());
Row = " where Row between " + StartNumber.ToString() + " and " + EndNumber.ToString();
}
return DbHelperSQL.Query("with temptbl AS (SELECT ROW_NUMBER() OVER (ORDER BY " + parameters[3].SqlValue.ToString() + ")AS Row, " + parameters[1].SqlValue.ToString() + " FROM " + TableWhere + ") SELECT * FROM temptbl" + Row);
SELECT
a.*,CASE WHEN b.spgid IS NOT NULL THEN b.payzt ELSE 0 END AS payzt
FROM shipin AS a
LEFT JOIN (SELECT spgid,MAX(payzt) AS payzt FROM dingdan WHERE hygid='1052BE4B-174F-44C6-8B83-44AB8352C4FE' GROUP BY spgid) AS b ON a.gid=b.spgid
WHERE a.qupy=1 AND a.qpyfl=3 --外面传进来的值
--1052BE4B-174F-44C6-8B83-44AB8352C4FE
SELECT
a.*,max(CASE WHEN b.spgid IS NOT NULL THEN b.payzt ELSE 0 END) AS payzt
FROM shipin AS a
LEFT JOIN (SELECT spgid,MAX(payzt) AS payzt FROM dingdan WHERE hygid='1052BE4B-174F-44C6-8B83-44AB8352C4FE' GROUP BY spgid) AS b ON a.gid=b.spgid
WHERE a.qupy=1 AND a.qpyfl=3 --外面传进来的值
消息 8120,级别 16,状态 1,第 2 行
选择列表中的列 'shipin.gid' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
还是提示这个SELECT
a.*,max(CASE WHEN b.spgid IS NOT NULL THEN b.payzt ELSE 0 END) AS payzt
FROM shipin AS a
LEFT JOIN (SELECT spgid,MAX(payz) AS payzt FROM dingdan WHERE hygid=@hygid GROUP BY spgid) AS b ON a.gid=b.spgid
WHERE a.qupy=@qupy AND a.qpyfl=@qpyfl --外面传进来的值
SELECT
a.*,max(CASE WHEN b.spgid IS NOT NULL THEN 1 ELSE 0 END) AS payzt
FROM shipin AS a
LEFT JOIN (SELECT spgid FROM dingdan WHERE hygid=@hygid GROUP BY spgid) AS b ON a.gid=b.spgid
WHERE a.qupy=@qupy AND a.qpyfl=@qpyfl --外面传进来的值
[/quote]
这个不行 还是报错
消息 8120,级别 16,状态 1,第 2 行
选择列表中的列 'shipin.gid' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。SELECT
a.*,max(CASE WHEN b.spgid IS NOT NULL THEN 1 ELSE 0 END) AS payzt
FROM shipin AS a
LEFT JOIN (SELECT spgid FROM dingdan WHERE hygid=@hygid GROUP BY spgid) AS b ON a.gid=b.spgid
WHERE a.qupy=@qupy AND a.qpyfl=@qpyfl --外面传进来的值
SELECT
a.gid,max(CASE WHEN b.spgid IS NOT NULL THEN 1 ELSE 0 END) AS payzt
FROM shipin AS a
LEFT JOIN dingdan AS b ON a.gid=b.spgid AND b.hygid=@hygid --外面传进来的值
WHERE a.qupy=@qupy AND a.qpyfl=@qpyfl --外面传进来的值
[/quote]
消息 8120,级别 16,状态 1,第 2 行
选择列表中的列 'shipin.gid' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
替换参数还是这个错误///[/quote]
SELECT
a.gid,max(CASE WHEN b.spgid IS NOT NULL THEN 1 ELSE 0 END) AS payzt
FROM shipin AS a
LEFT JOIN dingdan AS b ON a.gid=b.spgid AND b.hygid=@hygid --外面传进来的值
WHERE a.qupy=@qupy AND a.qpyfl=@qpyfl --外面传进来的值
GROUP BY a.gid
SELECT s.gid,
ISNULL(d.payzt,0) payzt
FROM shipin s
LEFT JOIN dingdan d
ON s.gid = d.gid
AND d.spgid = @spgid
AND d.hygid = @hygid
SELECT
a.gid,max(CASE WHEN b.spgid IS NOT NULL THEN 1 ELSE 0 END) AS payzt
FROM shipin AS a
LEFT JOIN dingdan AS b ON a.gid=b.spgid AND b.hygid=@hygid --外面传进来的值
WHERE a.qupy=@qupy AND a.qpyfl=@qpyfl --外面传进来的值
[/quote]
消息 8120,级别 16,状态 1,第 2 行
选择列表中的列 'shipin.gid' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
替换参数还是这个错误///