SQL:需要有关SQL查询的帮助 [英] SQL: Need help with SQL Query

问题描述

嗨
我有五个数据类型为int的列，我已经在主键的基础上选择了行，但是现在我要在该行中选择名称值小于某个int值的特定列.

结果列是这五个列中的任何一个

请帮我，我在Google上找不到解决方案，我也有要选择的列名称(第一年龄，第二年龄，第三年龄，第四年龄，第五年龄中的一个)，该列的值让我们假设<< 20

  USE  [MedicalLabDB]
转到
/*   *****对象:表[dbo].[Advance_Sub_Tests]脚本日期:01/11/2012 15 :33:11 ******/
设置  ANSI_NULLS  打开
转到
设置  QUOTED_IDENTIFIER  >打开
转到
创建 表 [dbo].[Advance_Sub_Tests](
[Advance_Sub_Test_ID] [ int ]  IDENTITY ( 1 ， 1 )不  NULL ，
[Sub_Test_ID] [ int ]  NULL ，
[L_Bound_M_LessThan_First_Age] [浮动]  NULL ，
[U_Bound_M_LessThan_First_Age] [浮动]  NULL ，
[L_Bound_M_LessThan_Second_Age] [ float ]  NULL ，
[U_Bound_M_LessThan_Second_Age] [ float ]  NULL ，
[L_Bound_M_LessThan_Third_Age] [ float ]  NULL ，
[U_Bound_M_LessThan_Third_Age] [浮动]  NULL ，
[L_Bound_M_LessThan_Fourth_Age] [ float ]  NULL ，
[U_Bound_M_LessThan_Fourth_Age] [ float ]  NULL ，
[L_Bound_M_LessThan_Fifth_Age] [ float ]  NULL ，
[U_Bound_M_LessThan_Fifth_Age] [ float ]  NULL ，
[L_Bound_F_LessThan_First_Age] [浮动]  NULL ，
[U_Bound_F_LessThan_First_Age] [浮动]  NULL ，
[L_Bound_F_LessThan_Second_Age] [ float ]  NULL ，
[U_Bound_F_LessThan_Second_Age] [ float ]  NULL ，
[L_Bound_F_LessThan_Third_Age] [ float ]  NULL ，
[U_Bound_F_LessThan_Third_Age] [ float ]  NULL ，
[L_Bound_F_LessThan_Fourth_Age] [浮动]  NULL ，
[U_Bound_F_LessThan_Fourth_Age] [ float ]  NULL ，
[L_Bound_F_LessThan_Fifth_Age] [ float ]  NULL ，
[U_Bound_F_LessThan_Fifth_Age] [ float ]  NULL ，
[First_Age] [ int ]  NULL ，
[Second_Age] [ int ]  NULL ，
[Third_Age] [ int ]  NULL ，
[Fourth_Age] [ int ]  NULL ，
[Fifth_Age] [ int ]  NULL ，
[Test_Active] [位<​​/span>]  NULL ，
[版本] [时间戳]  NULL ，
  CONSTRAINT  [PK_Advance_Sub_Test_1]  PRIMARY   KEY  集群
(
[Advance_Sub_Test_ID]  ASC 
) WITH (IGNORE_DUP_KEY = 关闭)打开 [ PRIMARY ]
)打开 [ PRIMARY ] 

解决方案

内容:(不幸的是，这将重复返回同一行，其次数是≤20的年龄列，但是最后一列(AgeGroup)将告诉您哪个年龄"是< 20 ...)

  SELECT  *，'  First_Age'  as  '  AgeGroup'跨度>
 FROM  [dbo].[Advance_Sub_Tests]
位置 First_Age<  20 
联盟(Union) 所有
 SELECT  *，'  Second_Age'  as  '  AgeGroup'
 FROM  [dbo].[Advance_Sub_Tests]
位置 Second_Age<  20 
联盟(Union) 所有
选择 *，'  Third_Age'  as  '  AgeGroup'
 FROM  [dbo].[Advance_Sub_Tests]
位置 Third_Age<  20 
联盟(Union) 所有
选择 *，' 第四年龄'  as  '  AgeGroup'
 FROM  [dbo].[Advance_Sub_Tests]
位置 Fourth_Age<  20 
联盟(Union) 所有
选择 *，' 第五年龄'  as  '  AgeGroup'
 FROM  [dbo].[Advance_Sub_Tests]
位置 Fifth_Age<  20  

hi
i have five colums which has data type int, i have selected row on the basis of primary key, but now in this row i want to select particular column with name which has value less than some int value.

the resulted column is any in this five column

please help me i find no solution for that on google also i have that table i want to select the column name (which is one among first age,second age,thirdage,fourth age,fifth age) which has the value let assume <20

USE [MedicalLabDB]
GO
/****** Object:  Table [dbo].[Advance_Sub_Tests]    Script Date: 01/11/2012 15:33:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Advance_Sub_Tests](
	[Advance_Sub_Test_ID] [int] IDENTITY(1,1) NOT NULL,
	[Sub_Test_ID] [int] NULL,
	[L_Bound_M_LessThan_First_Age ] [float] NULL,
	[U_Bound_M_LessThan_First_Age ] [float] NULL,
	[L_Bound_M_LessThan_Second_Age] [float] NULL,
	[U_Bound_M_LessThan_Second_Age] [float] NULL,
	[L_Bound_M_LessThan_Third_Age] [float] NULL,
	[U_Bound_M_LessThan_Third_Age] [float] NULL,
	[L_Bound_M_LessThan_Fourth_Age] [float] NULL,
	[U_Bound_M_LessThan_Fourth_Age] [float] NULL,
	[L_Bound_M_LessThan_Fifth_Age] [float] NULL,
	[U_Bound_M_LessThan_Fifth_Age] [float] NULL,
	[L_Bound_F_LessThan_First_Age] [float] NULL,
	[U_Bound_F_LessThan_First_Age] [float] NULL,
	[L_Bound_F_LessThan_Second_Age] [float] NULL,
	[U_Bound_F_LessThan_Second_Age] [float] NULL,
	[L_Bound_F_LessThan_Third_Age] [float] NULL,
	[U_Bound_F_LessThan_Third_Age] [float] NULL,
	[L_Bound_F_LessThan_Fourth_Age] [float] NULL,
	[U_Bound_F_LessThan_Fourth_Age] [float] NULL,
	[L_Bound_F_LessThan_Fifth_Age] [float] NULL,
	[U_Bound_F_LessThan_Fifth_Age] [float] NULL,
	[First_Age] [int] NULL,
	[Second_Age] [int] NULL,
	[Third_Age] [int] NULL,
	[Fourth_Age] [int] NULL,
	[Fifth_Age] [int] NULL,
	[Test_Active] [bit] NULL,
	[Version] [timestamp] NULL,
 CONSTRAINT [PK_Advance_Sub_Test_1] PRIMARY KEY CLUSTERED 
(
	[Advance_Sub_Test_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

解决方案

what about: (this will unfortunately return the same row repeatedly, as many times as what there are age-columns < 20, but the last column (AgeGroup) will tell you which ''age'' was < 20...)

SELECT *, 'First_Age' as 'AgeGroup'
FROM [dbo].[Advance_Sub_Tests]
WHERE First_Age < 20
UNION ALL
SELECT *, 'Second_Age' as 'AgeGroup'
FROM [dbo].[Advance_Sub_Tests]
WHERE Second_Age < 20
UNION ALL
SELECT *, 'Third_Age' as 'AgeGroup'
FROM [dbo].[Advance_Sub_Tests]
WHERE Third_Age < 20
UNION ALL
SELECT *, 'Fourth_Age' as 'AgeGroup'
FROM [dbo].[Advance_Sub_Tests]
WHERE Fourth_Age < 20
UNION ALL
SELECT *, 'Fifth_Age' as 'AgeGroup'
FROM [dbo].[Advance_Sub_Tests]
WHERE Fifth_Age < 20

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