How to combine and display the first few records in the database table Delphi/Windows SDK/API
Http://www.delphi2007.net/DelphiDB/html/delphi_20061222055338173.html
There is a database table with four fields. The first field is ID, and the second, third, and fourth fields are character fields such:
Id str1 str2 str3
1 Z l m
2 l m n
3 l m n
4 l m n
.....
On Program To display
Id str1 str2 str3 addstr
1 Z l m
2 l m n
3 l m n
4 l m n
5 n l zlmlmnlmnlmnnnl
6 L n o lmnlmnlmnnnllno
.....
That is to say, the content of the newly added field is the merging and display of the first five records (including their own records), 1, 2, and 3 fields. You guys may have a trick ....
10 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~ Points ?!
I don't know about the market. How much should I score?
If exists (select 1 from sysobjects where id = object_id (N 'testtable '))
Drop table testtable
Create Table testtable
(ID int Primary Key Identity (1, 1 ),
Str1 varchar (1 ),
Str2 varchar (1 ),
Str3 varchar (1 ),
Addstr varchar (100)
)
Insert into testtable values ('Z', 'l', 'M ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M','t ','')
Go
Alter procedure mytestprocedure
@ Fcurrentid int
As
Declare
@ Fieldstr varchar (100 ),
@ Maxid int,
@ Currentid int
Set @ fieldstr =''
Set @ currentid = @ fcurrentid + 1
Select @ maxid = max (ID) from testtable
Select @ fieldstr = @ fieldstr + str1 + str2 + str3 from testtable where ID <= @ fcurrentid
Update testtable set addstr = @ fieldstr where id = @ fcurrentid
While @ currentid <@ maxid + 1
Begin
Select @ fieldstr = right (@ fieldstr, Len (@ fieldstr)-3 * Len (str1) + str1 + str2 + str3 from testtable where id = @ currentid
Update testtable set addstr = @ fieldstr where id = @ currentid
Set @ currentid = @ currentid + 1
End
Go
Go
Exec mytestprocedure 5
Select * From testtable
Execution result
------------------
1zlm
2lmn
3lmn
4lmn
5 lmnzlmlmnlmnlmnlmn
6 lmnlmnlmnlmnlmnlmn
7 lmnlmnlmnlmnlmnlmn
8 lmnlmnlmnlmnlmnlmn
9 lmtlmnlmnlmnlmnlmt
The while loop in the middle can also be implemented using a cursor, but the results are similar,
There should be a simpler way to deal with it in one sentence, but I didn't expect it for the moment.
Up
The following solution, Code It may be less or easier to understand, but the number of internal executions seems to be more.
If exists (select 1 from sysobjects where id = object_id (N 'testtable '))
Drop table testtable
Create Table testtable
(ID int Primary Key Identity (1, 1 ),
Str1 varchar (1 ),
Str2 varchar (1 ),
Str3 varchar (1 ),
Addstr varchar (100)
)
Insert into testtable values ('Z', 'l', 'M ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M', 'n ','')
Insert into testtable values ('l', 'M','t ','')
Go
If exists (select 1 from sysobjects where id = object_id (N 'myfunction '))
Drop function myfunction
Go
-- A user-defined function returns a string of the first few rows of the current ID based on the passed founding ID (that is, starting from the first line;
Create Function myfunction (@ finitid int, @ fcurrid INT)
Returns varchar (100)
As
Begin
Declare
@ Fielddstr varchar (100)
Set @ fieldstr =''
Select @ fieldstr = @ fieldstr + str1 + str2 + str3
From testtable
Where ID between (@ fcurrid-@ finitid + 1) and @ fcurrid
Return (@ fieldstr)
End
Go
Update testtable set addstr = test. DBO. myfunction (5, ID) from testtable where ID> 4
Select * From testtable
Create Function AddColumn (@ id int)
Returns nvarchar (4000)
Begin
Declare @ SQL nvarchar (4000)
Declare @ row int
Select @ SQL =''
Select @ ROW = count (1) from (select top 5 * From Tadd where ID <= @ ID order by id desc) T
If @ ROW = 5
Begin
Select @ SQL = @ SQL + STR from (select top 5 str1 + str2 + str3 STR from (select top 5 * From Tadd where ID <= @ ID order by ID DESC) t order by ID) TT
End
Return @ SQL
End
-----------------------------
Select ID, str1, str2, str3, DBO. addatr (ID) addstr from Tadd
Select ID, str1, str2, str3, DBO. AddColumn (ID) addstr from Tadd
Thank you for your answers. However, there is still some difference between this and my goal. I may not be clear about it. I want to keep the database intact and add a field to adotable of Delphi, in the oncalcfields event, how can I achieve the above purpose!
Rewrite VCL
After Continuous experiments, I solved the problem by myself, which is quite troublesome. But thank you very much. I will give you some points to the two handsome guys who answered the question positively !!
To solve this problem, add a calculated field as the table does not move. Then, create a custom function, pass the ID to the function, and include a dynamic query in the function, perform a loop query to accumulate the results and return the values to the calculated fields in the oncalcfields process of table.