Home>
How can I use SQL server to display the number of occurrences of a record with the same condition in SQLserver?
I can display the number of occurrences, but I want to display the number of occurrences for the record, as in the "nth" below.
↓ I want to get the number of times the same person has visited the same place. Time series is ascending ID
ID | name | Location | nth |
---|---|---|---|
001 | A-san | Tokyo | 1 |
002 | B-san | Osaka | 1 |
003 | C-san | Tokyo | 1 |
004 | A-san | Tokyo | 2 |
005 | C-san | Tokyo | 2 |
006 | A-san | Osaka | 1 |
007 | B-san | Tokyo | 1 |
008 | C-san | Tokyo | 3 |
-
Answer # 1
-
Answer # 2
It seems to be easy if you use the ranking function prepared for each RDB
It is like this when writing SQL general purposecreate table tbl (id int primary key, name varchar (10), place varchar (10)); insert into tbl values (1, 'A', 'Tokyo'), (2, 'B', 'Osaka'), (3, 'C-san', 'Tokyo'), (4, 'A', 'Tokyo'), (5, 'C', 'Tokyo'), (6, 'Mr. A', 'Osaka'), (7, 'B', 'Tokyo'), (8, 'Csan', 'Tokyo'); select id, name, place, (select count (*) + 1 from tbl where name = t1.name and place = t1.place and id
Related articles
- sql server - how to write a condition for a stored procedure
- cannot add record to sql server in vbnet
- about trigger trigger condition of sql server
- sql server - about the join condition at the time of self-join
- there is a record w in some of the tables in microsoft's sql server all items are the same is there a convenient sql statement t
- how to display mistake in search condition of where clause in sql
- when issuing sql that takes time to process to sql server with powershell, i want to avoid the timeout and finish the process
- sql server - permission with windows authentication of sqlserver
- [sql server] how to join tables with different layouts
- [sql server] about commit of trigger
- there is an unfamiliar column definition in the microsoft sql server view definition i want to know if it is a sequence generate
- garbled characters when accessing microsoft odbc driver for sql server via apache
- [sql server, sales total] the sales amount for each hour is aggregated in the "morning column" and "afternoon col
- sql server - it's about sql injection
- sql server - i want to add a csv file to db with bulk insert
- sql server i want to judge whether a certain character (varchar type) is an integer by case statement
- sql server - maximum number of cases per day in sql
- sql server - for a single property, in view, enter multiple input items separately and combine them when inserting into the db
- sql server - expansion (release) of transaction log (*ldf) area
- sql server - about conditional expressions in dynamic sql
Related questions
- i want to know how to specify a window numerically on sql server
- i want to create a log table in mysql
- sql server - code to group by sql and delete the row if the number is 2nd higher
- c# cannot connect to sql server
- data entry from php to sql
- javascript - i want to enumerate ids of only records that meet a specific condition using sql
- about sql code in sql server
- a php program that searches for data registered in sql by "year-month-day" by "year and month"
- sql server - i want to obtain the result by excluding a specific value from the duplicate values in sql
- ms sql server table csv export, string conversion error
That ’s it.