两个查询有问题 [英] Problem with two queries

问题描述

我得到了以下查询:

I have got this query:

SELECT tbAttributes.*, 'MIN' AS Range, (Attribute_position * 10) AS Attribute_Sorting 
FROM tbAttributes 

UNION
 
SELECT tbAttributes.*, 'TAR' AS Range, (Attribute_position * 10 + 1) AS Attribute_Sorting 
FROM tbAttributes 

UNION 

SELECT tbAttributes.*, 'MAX' AS Range, (Attribute_position * 10 + 2) AS Attribute_Sorting 
FROM tbAttributes

我想在其他查询中使用此查询的结果.

我确实尝试将此查询的结果发送到DataSet:

I would want to use results of this query in other query.

I did try to send result of this query to DataSet:

DataSet Ds_kw_Attributes_x3 = new DataSet();

Ds_kw_Attributes_x3 = Procs.FillDataSet(
"SELECT tbAttributes.*, ''MIN'' AS Range, (Attribute_position * 10) AS Attribute_Sorting FROM tbAttributes 
UNION 
SELECT tbAttributes.*, ''TAR'' AS Range, (Attribute_position * 10 + 1) AS Attribute_Sorting FROM tbAttributes 
UNION 
SELECT tbAttributes.*, ''MAX'' AS Range, (Attribute_position * 10 + 2) AS Attribute_Sorting FROM tbAttributes");

然后这个:

And then this:

string Attrib_posi = Ds_kw_Attributes_x3.Tables[0].Columns["Attribute_position"].ToString();
string Range = Ds_kw_Attributes_x3.Tables[0].Columns["Range"].ToString();
string Attri_ColName = Ds_kw_Attributes_x3.Tables[0].Columns["Attribute_ColName"].ToString();

string Attrib_Name = Ds_kw_Attributes_x3.Tables[0].Columns["Attribute_Name"].ToString();

最后，我确实尝试在第二个查询中使用它:

And finnaly I did try to use it in second query:

return "SELECT * FROM [SELECT " + Attrib_posi + " * 10 + (CASE UPPER(" + Range + ") WHEN ''TAR'' THEN 1 WHEN ''MAX'' THEN 2 ELSE 0 END) As Attribute_position, " + Attri_ColName + " As Id, " + Attrib_Name + " As Nazwa, " + Range + " As Zakres, " + AneksSql + " FROM " + Ds_kw_Attributes_x3 + " ]. As Attrib WHERE (NOT IsNull(Attrib.Specyfikacja)) " + AneksWhere;             

问题是我遇到了巨大的错误，首先我不知道是我写的所有方法的好方法，请帮忙.

The problem is I got Huge error, the first I don''t know is that good way of all I wrote, please help.

推荐答案

首先.为什么要两次调用数据库.我最喜欢的是，您在 WITH 中添加第一个查询结果，然后将其作为表传递给第二个查询.
这样，如果您告知收到的错误，我们将予以答复.
但是我建议您也使用WITH子句来减少数据库调用和复杂性...在编写查询时..

即

first of all..why you are making two calls to database. what i preffer is, you add first query result in WITH and then pass it to second query just as a table.
this way we would answer it, if you inform about the error you receive.
but i suggest you to use WITH clause for reducing a database call and complexity as well...while writing query..

i.e.

;WITH testIT
(
 select id,name,fatherName from student
)
select * from studentDetails details std
where  std.FK_studentID = testIT.id

这样，您就可以将子查询放入内部，并在一次调用中进行最终查询.

this way you put your sub query inside and make your final query within one call.

在代码中使用Query是一种不好的做法，最好将它们隐藏到Sp中然后再使用

您想阅读哪一行?

This is bad practice to use Query in the code, better you covert them into Sp and then use it

Which row you want to read?

string Attrib_posi = Ds_kw_Attributes_x3.Tables[0].Columns["Attribute_position"].ToString();

在此代码中，您正在访问表0，但没有给出任何特定的行.

应该是这样的

in this code, you are accessing Table 0, but not giving any particular row.

It should be something like this

string Attrib_posi = Ds_kw_Attributes_x3.Tables[0].Rows[0].Columns["Attribute_position"].ToString();

在C#+ Access中，它运行良好，但无法在其他查询中使用Access查询结果代码.

现在，我将展示它的工作方式.

所以我在Access中获得了Access查询，得到了1个查询名称:"kw_Attributes_x3"

In C# + Access I got it working well, but I can''t use Access querie result in others queries in code.

I will show now how it looksm like working.

So I got Access querie in AccessI got 1x query name: "kw_Attributes_x3"

SELECT tbAttributes.*, "MIN" AS Range, (Attribute_position * 10) AS Attribute_Sorting FROM tbAttributes 
UNION
SELECT tbAttributes.*, "TAR" AS Range, (Attribute_position * 10 + 1) AS Attribute_Sorting FROM tbAttributes
UNION SELECT tbAttributes.*, "MAX" AS Range, (Attribute_position * 10 + 2) AS Attribute_Sorting FROM tbAttributes;

还有一个使用此访问查询的查询所在的函数:

And a function where are queries which are using this Access querie:

private string GetGrdSpecSql()
		{		
			long SpecId;
			long[] AneksId;
			int i = 0;
			AneksId = new long[i + 1];

            SpecId = (Int32)DsSpecHeader.Tables[0].Rows[0]["Spec_ID"];
					
			foreach (DataRow dr in DsAneksHeader.Tables[0].Rows)
			{
				Array.Resize(ref AneksId, i + 1);
                AneksId[i] = (Int32)dr["Aneksy_ID"];
				i++;
			}

			string AneksSql = "(select SpecValue from tbSpec_data S where kw_Attributes_x3.Attribute_ColName = S.Attribute_ColName and UCASE(kw_Attributes_x3.Range) = UCASE(S.Range) and S.Spec_ID = " + SpecId + ") AS Specyfikacja";
			string AneksWhere = "";
			i = 1;
			foreach (long Aneks in AneksId)
			{
                if ((Int32)Procs.RetLng(Aneks) > 0)
				{
					AneksSql = AneksSql + ", (select SpecValue from tbAneksy_data " + Strings.Chr(System.Convert.ToInt32(65 + i)) + " where kw_Attributes_x3.Attribute_ColName = " + Strings.Chr(System.Convert.ToInt32(65 + i)) + ".Attribute_ColName and UCASE(kw_Attributes_x3.Range) = UCASE(" + Strings.Chr(System.Convert.ToInt32(65 + i)) + ".Range) and " + Strings.Chr(System.Convert.ToInt32(65 + i)) + ".Aneksy_ID = " + Aneks + ") AS Aneks" + i;
				}
                if ((Int32)Procs.RetLng(Aneks) > 0)
				{
					AneksWhere = AneksWhere + " or Attrib.Aneks" + i + " > 0 ";
				}
				i++;
			}
			
			return "SELECT * FROM [SELECT kw_Attributes_x3.Attribute_position * 10 + IIf(UCase(kw_Attributes_x3.Range)=\'TAR\',1,IIf(UCase(kw_Attributes_x3.Range)=\'MAX\',2,0)) As Attribute_position, kw_Attributes_x3.Attribute_ColName As Id, kw_Attributes_x3.Attribute_Name As Nazwa, kw_Attributes_x3.Range As Zakres, " + AneksSql + " FROM kw_Attributes_x3 ]. As Attrib WHERE (NOT IsNull(Attrib.Specyfikacja)) " + AneksWhere;			
		       
        }

问题是我现在在SQL Server中拥有所有权限.我知道存在类似CTE的东西.有人可以帮忙吗?

The poroblem is now I have all in SQL Server. I know there exist something like CTE. Could someone help do it?

这篇关于两个查询有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！