下表的SQL查询 [英] SQL Query for the following table

问题描述

大家好，

我想要通过SQL查询以下内容.

我的表格如下:

类别代码类别Marksthere Scalepoint
1学者0 5
2共学1 NULL
3部分-1 0 10


现在这是类别表，其中字段
categorycode是主键
类别是varchar字段
标记的位字段始终为0或1
scalepoint是整数字段

现在我需要的是一个查询，该查询将导致类别中是否存在标记，即当marksthere字段为(1)时，缩放点不应出现在结果输出中
但是如果Markesthere列没有标记，即为(0)，则在结果输出中应在其中包含比例点.
我需要一个SQL查询来选择所有字段....基于Marksthere
例如.

如果Marksthere为0
那么这些字段应该是categorycode，category，marksthere，scalepoint
但是当marksthere为1时
那么这些字段应该是categorycode，category，marksthere

请帮我解决我真正被卡住的问题.
提前谢谢..

问候，
Krunal Panchal

Hi everyone,

I want sql query for the following thing.

My table is as follows :

Categorycode Category Marksthere Scalepoint
1 scholastic 0 5
2 co-scholastic 1 NULL
3 part -1 0 10


Now here is the table of category where fields
categorycode is the primary key
category is the varchar field
marksthere is the bit field which is always 0 or 1
scalepoint is the integer field

Now what i need is a query which results if there is Marks in the category i.e when the marksthere field is (1) then the scale point should not come in the resultant output
But if the marksthere column has no marks i.e. it is (0) then in the resultant output should have the scalepoint in it.
I need a SQL query for selecting all the fields....on the basis of Marksthere
For eg.

if Marksthere is 0
then the fields should be categorycode, category,marksthere,scalepoint
but when marksthere is 1
then the fields should be categorycode,category,marksthere

Please help me out for the thing i am really stucked up..
Thanks in advance..

Regards,
Krunal Panchal

推荐答案

我不确定您是否要将结果绑定到任何显示控件(如GridView/Repeater)的数据中，否则您将对结果.

但是，如果要使用第一个选项，则需要以表格格式显示4列，即CategoryId，CategoryCode，MarksThere和ScalePoint.因此，根据您的要求条件，如果MarksThere为0，则ScalePoint应该以其他方式不显示.

在这种情况下，您必须具有ScalePoint的标头.您可以在MarksThere为1的那列中放置破折号，零或任何其他合适的文本.

因此，对于此选项，您只需编写如下查询即可.

I am not sure that you are going to bind the result in any data displaying control like GridView/Repeater or you are going to do some manipulation on the result.

But if you are going with first option then you will need to display 4 columns i.e CategoryId, CategoryCode, MarksThere, ScalePoint in the tabular format. So as per your required condition if MarksThere has 0 then ScalePoint should be displayed other wise not.

In that case you must need a header with ScalePoint. You can place dash, nil or any other suitable text in that column where MarksThere are 1.

So for this option you can simply write a query like below.

SELECT CategoryId, CategoryCode, MarksThere, CASE WHEN MarksThere = 0 THEN Convert(varchar,ISNULL(ScalePoint,0)) ELSE ''-'' END AS ScalePoint FROM TableName

希望对您有所帮助.

Hope it helps.

您可以通过使用两个单独的查询
来做到这一点.
意味着您必须两次访问数据库才能获取值.....如果您一次缺少一个col.

如果您想在2查询中，那么我会给您.

you can do that by using the two seperate query

means u have to hit database twice to get the values..... if u r missing one col at one time.

if u want in 2 query, then i''ll give u.

您要的内容在SQL中是不可能的. SQL查询返回固定数量的字段/列.查询不能返回四列结果的一行，然后不能返回两列相同结果的下一行.希望您能理解我要说的话.

抱歉.

What you are asking is not possible in SQL. An SQL Query returns a fixed number of fields/columns. A Query cannot return one row of a result with four columns, and then the next row of the same resultwith two columns. I hope you can understand what I am trying to say.

Sorry.

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