如何为下面提到的条件编写SQL查询 [英] how to write SQL query for below mentioned condition
本文介绍了如何为下面提到的条件编写SQL查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我必须从加载日期最大的一个表中选择数据,然后我必须将该数据粘贴到另一个表中加载日期是最大的但条件是,数据将在其他栏中随处可用。
注意:加载日期是列名。
请同时建议。
提前致谢。
--- -------------------------------------------------- ------
请在下面找到表结构:
CREATE TCREATE TABLE [dbo]。[table1](
[ACCT_ID] [< span class =code-keyword> char ]( 30 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SYS_NM] [ char ]( 9 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RPT_DT] [ smalldatetime ] NOT NULL ,
[CYCLE_CUT_DT] [ smalldatetime ] NOT NULL ,
[HI_SPEND_AM] [十进制]( 15 , 2 ) NOT NULL ,
[LOC_LMT_AM ] [十进制]( 15 , 2 ) NOT NULL ,
[LOAD_TS] [ smalldatetime ] NOT NULL CONSTRAINT [DF_t0705_HI_SPEND_ACCT2_LOAD_TS] DEFAULT (getutc date()),
CONSTRAINT [XUZAG01] PRIMARY KEY CLUSTERED
(
[ACCT_ID] ASC ,
[CYCLE_CUT_DT] ASC ,
[SYS_NM] ASC ,
[RPT_DT] ASC
) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ]
) ON [ PRIMARY ]
CREATE 表 [dbo]。[table2](
[ACCT_ID] [ char ] ( 30 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PREV_LOC_AM] [ decimal ] ( 15 , 2 ) NOT NULL ,
[LOC_CHNG_AM] [ decimal ]( 15 , 2 ) NOT NULL ,
[NEW_LOC_AM] [十进制]( 15 , 2 ) NOT NULL ,
[CHNG_DT] [ datetime ] NOT NULL ,
[ SYS_NM] [ char ]( 9 ) COLLATE SQL_Latin1 _General_CP1_CI_AS NOT NULL ,
[WEB_SRVC_STA_CD] [ char ]( 1 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ALLOC_ACT_CD] [ char ]( 1 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LOC_CHNG_TYPE_CD] [ char ]( 2 )< span class =code-keyword> COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OTHER_PTCP_ACCT_RISK_ROLLUP_CD] [ char ]( 2 ) COLL ATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CYC_CUT_DT] [ smalldatetime ] NOT NULL ,
[RPT_IN] [ char ]( 1 ) COLLATE SQL_Latin1_General_CP1_CI_AS < span class =code-keyword> NOT NULL ,
[LOAD_TS] [ smalldatetime ] NOT NULL
) ON [ PRIMARY ]
我 提取数据 LOC_LMT_AMT 列 来自 表 1 其中 CYCLE_CUT_DT < span class =code-keyword> max 和 粘贴相同的数据进入第二表 其中 [CHNG_DT] < span class =code-keyword> 最大值。
我有 粘贴数据,其中 CHNG_DT max,其余列应 解决方案
检查这个...
update table2
set
table2.col = table1.LOC_LMT_AMT - 因为你没有提到你需要更新哪个col
来自
table1
其中
table1.CYCLE_CUT_DT =( SELECT MAX(CYCLE_CUT_DT) FROM table1)
AND
t able2.CHNG_DT =( SELECT MAX(CHNG_DT) FROM table2)
希望这会对你有所帮助。
干杯
Hi,
I have to select data from one table where "load date" is max and then i have to paste that data into another table where "load date" is max. but the condition is, data will be available everywhere in the other columns.
Note: load date is a column name.
Please suggest on the same.
Thanks in advance.
-----------------------------------------------------------
Please find table structure below:
CREATE TCREATE TABLE [dbo].[table1](
[ACCT_ID] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SYS_NM] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RPT_DT] [smalldatetime] NOT NULL,
[CYCLE_CUT_DT] [smalldatetime] NOT NULL,
[HI_SPEND_AM] [decimal](15, 2) NOT NULL,
[LOC_LMT_AM] [decimal](15, 2) NOT NULL,
[LOAD_TS] [smalldatetime] NOT NULL CONSTRAINT [DF_t0705_HI_SPEND_ACCT2_LOAD_TS] DEFAULT (getutcdate()),
CONSTRAINT [XUZAG01] PRIMARY KEY CLUSTERED
(
[ACCT_ID] ASC,
[CYCLE_CUT_DT] ASC,
[SYS_NM] ASC,
[RPT_DT] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[table2](
[ACCT_ID] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PREV_LOC_AM] [decimal](15, 2) NOT NULL,
[LOC_CHNG_AM] [decimal](15, 2) NOT NULL,
[NEW_LOC_AM] [decimal](15, 2) NOT NULL,
[CHNG_DT] [datetime] NOT NULL,
[SYS_NM] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[WEB_SRVC_STA_CD] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ALLOC_ACT_CD] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LOC_CHNG_TYPE_CD] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OTHER_PTCP_ACCT_RISK_ROLLUP_CD] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CYC_CUT_DT] [smalldatetime] NOT NULL,
[RPT_IN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LOAD_TS] [smalldatetime] NOT NULL
) ON [PRIMARY]
I have to pickup the data of "LOC_LMT_AMT" column from table 1 where "CYCLE_CUT_DT" is max and have to paste the same data into second table where [CHNG_DT] is max.
I have to paste data , where "CHNG_DT" is max, remaining columns should not be pasted.
解决方案 Hi,
Check this...
update table2
set
table2.col=table1.LOC_LMT_AMT--since you have not mentioned in which col you have to update it
from
table1
where
table1.CYCLE_CUT_DT = (SELECT MAX(CYCLE_CUT_DT) FROM table1)
AND
table2.CHNG_DT = (SELECT MAX(CHNG_DT) FROM table2)
Hope this will help you.
Cheers
这篇关于如何为下面提到的条件编写SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文